PDA

View Full Version : Solved: VBA: Copy Worksheets to new Workbook & Rename Tabs from Vlookup



goobers
07-28-2009, 03:20 PM
I have scoured the existing threads and couldn't find anything that pertained to my problem. I have a workbook that has 3 worksheets it.

1) The first 2 worksheets need to be copied into a new workbook
2) and then need to be renamed based on a vlookup from the 3rd tab of the original workbook.

I have no problem copying my tabs to a new workbook. I am having issues with the vlookup that I am trying to create. So, in Sheet1 of the attached, Cell B2 has a value. When this sheet is copied to a new workbook, I would like my macro to rename Sheet1 to the value from the following Excel function if i wrote it manually:

=VLOOKUP('Sheet1'!B1,'Profit Centers'!A:B,2,FALSE)

I have tried many variations of the VBA vlookup from other resources on the internet and can't get any of them to work out properly.

Thanks in advance for any insight you all might have.

-Goobers

rbrhodes
07-28-2009, 03:49 PM
Hi g,

Put the lookup on sheet 3?


Sub copy()

Dim ShtName As String

'Change to wherever Vlookup is located
ShtName = Sheets("Profit Centers").Range("C2")

'No need to select
Sheets(Array("Sheet1", "Sheet2")).copy

'Rename sheet
Sheets("Sheet1").Name = ShtName

End Sub

goobers
07-28-2009, 05:37 PM
I guess I was complicating things a bit too much. Thanks so much for the help.