Help needed on variable workbook name.

[ebeth]

Hello,

I'm a novice VBA user and I would greatly appreciate any help on this as I have been searching the various Excel sites for several days and can't find the answer. I am trying to copy some data from one workbook to another while both workbooks are open. My code works perfectly when I use the file name of the second workbook to activate it, but the file names will be changing each month so I want to be able to store the name of the second workbook in a cell on the first workbook and use the cell reference in the line that activates the second workbook. I've tried some variations on this, but can't get anything to work. It seems like something that would be used regularly, so I'm hoping that someone will be able to help.

Thanks in advance,
ebeth:dunno

[austenr]

Welcome to VBAX!! Can you post your code?

[ebeth]

Hello austenr,
I'm at home and don't have my code here, but I will post it tomorrow.
Thanks very much for responding!
ebeth

[Bob Phillips]

How does the second workbook get opened, is it from code within the first or by user action?

[ebeth]

Hello xld,

The workbook is opened by the user. There are 7 users and each has a different region, which is reflected in the name of the workbooks along with the new period (we use 13 periods rather than 12 months). So I want to be able to have the name of the workbook for the specific region and the new period in a cell on the 1st workbook in order to be able to use the same macro for all regions and all periods.

Thanks!
ebeth

[Bob Phillips]

If you know the name of the regions, could you cycle through all of the opened workbooks looking for a match?

[ebeth]

xld,

I'm not sure. I do pretty rudimantary macros at this point. The period number will change each period as well and I don't want to have to go into the macro and update it each period for everyone.

Here is the code that I have that works fine with the actual workbook name in it. I have some things commented out that I was experimenting with. I want to put the workbbook name of the second workbook in cell E1 and reference E1 rather than the NE pd 9_2.xls in the line that Activates the other workbook.

Sub NewCustomer()

Dim Counter As Integer
Dim CurPdRpt As Workbook
Dim CurPdData As Workbook

Workbooks("Data Temp4.xls").Activate
Worksheets("Sales1").Select
'CurPdRpt = Range("E1")
'In1Not2 = Range("O4:O196")
Counter = Range("Q3")
Do Until Counter = 0
On Error GoTo NotFound
Find_Range("New", Range("Q4:Q196")).Select
Selection.Offset(0, -9).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Offset(0, 1).Select
Selection.Copy
Workbooks("NE pd 9_2.xls").Activate
Worksheets("PD Working").Activate
Range("A8").Select
ActiveCell.End(xlDown).Select
Selection.Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Workbooks("Data Temp4.xls").Activate

Worksheets("Sales1").Select

Loop
NotFound:
End Sub

[ebeth]

Sorry, I lost the indenting when I included my code.