Use cell value as new worksheet's name

feisofei

New Member
Joined
Dec 14, 2018
Messages
2
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.

8o6mUXF


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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Welcome to the forum :)

Is this what you want?
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


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 ]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top