vba for connecting sheets and variables

Question

I need your help if possible, on the following:

I have 2 workbooks, each one with the same sheets but each workbook has different data. Sheets are named after the client they have data for. i.e Sheet1 = John Sheet 2 = Andrew etc.

In workbook2, I need to fill in some data from workbook 1, but to the correct sheet each time. I mean, I want in workbook 2, at F32 to get the data that are in workbook 1 in cell T4, but for the same client.(sheet= john, will take the data that corresponds to sheet= john - of workbook1) both workbooks have more than 100 sheets. And I want to copy to the sheets of workbook b in cell F32 the data from workbook A cell T4 (but on each sheet , with the same sheetname)

On workbook B I have a template sheet that is beeing copied every time I need another sheet, so I would like to apply the code on the template I don't know if I explain well what I need

Some very kind expert proposed this..

Dim workbookA as workbook, workbookB as workbook, sheetA as worksheet, sheetB as worksheet
Set workbookA = workbooks("WorkbookA.xlsm")
Set workbookB = workbooks("WorkbookB.xlsm")
Set sheetA = workbookA.Worksheets("sheetA")
Set sheetB = workbookA.Worksheets("sheetB")
sheetB.Range("F32").copy sheetA.Range("T4")

and it works (I changed some minor mistyped mistakes) but only if I set the name on each variable sheetA=workbookA.Worksheets("john") sheetB = workbookB.Worksheets("john")

But I can't do that for 100 names and more

He also proposed to use :

" If you have a list of worksheets you could make the worksheet variables dynamic:"

Set sheetB = workbooks.Worksheets(sheetA.Range("A2").Value)

etc

BUT i don't understand what I must do. Can someone help me? I don't know much about variables

Adach1979 Adach1979 · Accepted Answer · 2014-05-28T12:58:49

You can use something like this

Sub CopyData()

Dim workbookA As Workbook, workbookB As Workbook, sheetA As Worksheet, sheetB As Worksheet, ws As Worksheet, sheetName As String
Set workbookA = Workbooks("WorkbookA.xlsm")
Set workbookB = Workbooks("WorkbookB.xlsm")

'Loop through each worksheet in Book A
For Each ws In workbookA.Worksheets
    Set sheetA = workbookA.Worksheets(ws.Name)
    Set sheetB = workbookB.Worksheets(ws.Name)
    sheetB.Range("F32").Copy sheetA.Range("T4")
Next
End Sub

vba for connecting sheets and variables

1 Answers