Use cell value as new worksheet's name

[feisofei]

Hi, I am new to VBA. I would like to name the sheet in new workbook as Range("B2").


However, Range("B2") refers back to the B2 in original sheet instead of the new worksheet.


The problem is when I apply filter for column B in original sheet and copy data to new worksheet, for example, the B2 in new worksheet is the B34 in the original sheet. Then the new sheet name is still range B2 in original sheet, but not the range B2 of the new sheet.

Sub test()


test1 = ActiveSheet.Range("B2") 'Sheetname


Sheets("Original").Range("a1").CurrentRegion.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveSheet.Name = test1


End Sub

I know I can simply do ActiveSheet.Name = Range("b2"), but is there anyway to make the variable refer to the B2 of new sheet ?

Thank you.

 

[Yongle]

Welcome to the forum

Is this what you want?

Sub test()
    Dim wb As Workbook
    Sheets("Original").Range("a1").CurrentRegion.Copy
    Set wb = Workbooks.Add
    With wb.Sheets(1)
        .Paste
        .Name = .Range("B2")
    End With
End Sub

When posting code click on # icon above post window and paste your code between the code tags which appear (it will look as above)
(with longer code it makes it much easier to read - and it is formatted as in the VBA window)
[ CODE ] Sub test()
Dim wb As Workbook
Sheets("Original").Range("a1").CurrentRegion.Copy
Set wb = Workbooks.Add
With wb.Sheets(1)
.Paste
.Name = .Range("B2")
End With
End Sub[ /CODE ]

 

[feisofei]

This is exactly what I want. Thank you Yongle !!!!!!

 

[Yongle]

thanks for feedback