VBA to Set a sheetname as a Variable and call from another workBook

[simora]

I need to set the ActiveSheet.Name in BK1 as a variable to call it from another workbook BK2

Specifically:

I'm trying to open BK2 sheet X automatically with a macro if I am in
BK1 sheet X

I want to match the new sheet number in BK2 to the current sheet number in BK1 that is the current ActiveSheet.
So If I am in BK1 Sheet3 I want to post data to BK2 Sheet 3 etc..etc
So If I am in BK1 Sheet5 I want to post data to BK2 Sheet 5 etc..etc

I am not looping through these sheets, I am posting to individual sheets one at a time as I get to them over a period of time.
Any sample code appreciated.

Thanks

[mikerickson]

Workbooks("BK2.xls").Sheets(ActiveSheet.Index).Range("A1") = "data"

[simora]

mikerickson:

Can you please explain your reply. I dont understand what your code
Range("A1") = "data"
is doing, or trying to do.

Seems like I need something along the lines of

Dim strSheetName As String
strSheetName = ActiveSheet.Name

when I am in BK1

But I cant get the sheet name passed to BK2 when I open it.

After I activate BK2 I get an error at

Sheets("strSheetName").Select

Hope you see the problem

Thanks

[Bob Phillips]

Sheets(strSheetName).Select

[simora]

XLD

Thanks for the effort, but that did not solve the problem. I still cant seem to pass the value of the sheet name to the 2nd workbook to match up the sheet name

[Bob Phillips]

That was to work with the code that you had come up with.

[simora]

It did not. I've still got the same problem. Can you help?

[Kenneth Hobs]

Simora, Sheets("strSheetName").Select should be: Sheets(strSheetName).Select

Of course that just selects the activesheet as you defined it. If you want to put some data into the other workbook's sheet with the same index as the activesheet's index to A1, then Mike's code is the ticket.

If you want to select the sheet and cell A1 as Mike defined it (with both workbooks open), then:
Sub t()
Dim i As Integer
i = ActiveSheet.Index
Workbooks("BK2.xls").Activate
Worksheets(i).Select
Range("A1").Select
End Sub
You may not have the index relationship that you think though.

[simora]

RE: Select should be: Sheets(strSheetName).Select

That is still not working.
How do I index both workbooks so that I can set
sheet 1 on book1 to sheet 1 on book2
sheet 5 on book1 to sheet 5 on book2
etc.. etc..

That should solve the problem.

Thanks

[simora]

Kenneth:
Thanks for pointing me in the right directon.

On Workbook 1
Dim i As Integer
i = ActiveSheet.Index
use when on both WorkBooks & sheets

This is the 2nd WorkBook
Application.Workbooks("Customer2008").Activate
Worksheets(i).Select

Got it to work.

Thanks