Changing Workbook names in VBA based on Open Workbook

Hi Guys

Hopefully somebody can help.

I currently have two workbooks.

Workbook 1 contains data that is entered into a table.
Workbook 2 contains data that is pulled from Workbook 1.

I have set up a button in Workbook 1 that will create a new Workbook 2 named the value of a certain cell. (Say C12).

This all works well, but now the data wont pull into the new workbook as Workbook 1 contains VBA referencing the Original name of Workbook 1.

Is there any way to change the VBA to automatically reference the new name of workbook 2.

Please help.

Thanks

Matt

Hi
It will be similar to the way you created new workbook with C12. You should share your codes so that we can suggest the change needed.
Ravi

Hi Ravi

Here is a sample of my code

To Save as a separate workbook.

-------------------------------------------------------
Option Explicit
Sub SaveAsSample()
Dim FName As String
Dim FPath As String

FPath = "C:"
FName = Sheets("Sheet1").Range("C12").Text
Workbooks("Sample.xls").SaveAs Filename:=FPath & "\" & FName
End Sub
--------------------------------------------------------
That creates my new workbook with the Filename based on the value of C12 in Workbook 1.

Now in workbook 1 I have VBA code that copies data from Wbook1 to "Sample.xls". As follows:

---------------------------------------------------------
Sub CopyFromSample()
NextRow = Workbooks ("Sample.xls").Sheets("sheet1").Range("A65536").End(xlUp).Row
Workbooks("Sample.xls").Sheets("sheet1").Cells(NextRow, 1).Resize(1,1).Value=Array(_ cells being copied.....

---------------------------------------------------------
The bits in red are the bits that need to change depending on the value in C12.

What do you think

Thanks

Matt

Hi
try

FName = Sheets("Sheet1").Range("C12")
nextrow = Workbooks(FName).Sheets("sheet1").Range("A65536").End(xlUp).Row

Note cell c12 should have filename with extension (ABC.xls) other wise use FName = Sheets("Sheet1").Range("C12") & ".xls"
Ravi

Hey Ravi

That worked a treat. Your a champ. Thanks for the help.

Matt