Get VBA to match cell data with a worksheet name and then copy and paste a cell

[Maaarie]

Good Morning,

I've got two workbooks, one named "Synthese" and another one named "Equity".

In the workbook "Equity", I've got different worksheets which names correspond to the general ledger's accounts. My file "Synthese" gathers in column B the names of all the accounts I may found in my file "Equity"'s worksheets.

I'd like to know if there's a VBA code that will allow me to copy the value of the cell E60 for each worksheet of my EQUITY and then paste it in the proper line of my file Synthese.

For example :

If the value of the cell B2 in the Synthese file is 145000, I want the VBA Code to look for the worksheet named 145000 in the file EQUITY, go into the worksheet 145000, copy the cell E60 and then paste it in the workbook Synthese in cell H2


Due to company policy I am unable to provide you a file.

Below is what I already did :

Sub Sheet2Sheet()
'
' Sheet2Sheet Macro
Dim rData As Range
Dim rCell As Range
Dim rFrom As Range
Dim rTo As Range
Dim lRightCol As Long

Workbooks.Open ("EQUITY.xlsx")
Windows("Equity.xlsx").Activate
With ActiveSheet
lRightCol = Range("E60")

Windows("0 - 108 - Fichier Synthese.xlsx").Activate
Set rData = Range(Range("B7"), Range("B" & Rows.Count).End(xlUp))
Windows("0 - 108 - Fichier Synthese.xlsx").Activate
For Each rCell In rData
Set rTo = Range("H" & Rows.Count)
Range("E60").Copy
Next
Windows("0 - 108 - Fichier Synthese.xlsx").Activate
Application.Goto Reference:="Sheet2Sheet"
End Sub

[JLGWhiz]

This might work. It assumes that the Synthese workbook is already open but EQUITY is not. If EQUITY is open when the code runs, it will display an alert message telling you that the file is already open.

Please Login or Register  to view this content.

[Maaarie]

Thank you for your quick answer.

It's not working I have Run-time Error 9 on the line below :

Set sh = wb2.Sheets(syn.Range("B2").Value)

Also, how can I adapt your code to all the lines in column B?

Thanks

[JLGWhiz]

I was not sure that would work since I could not see what cell B2 of the worksheet in the Synthese workbook actually contained. I based it on your statement that it would contain a worksheet name. If there is no name in cell B2 or if the entry in B2 is not a worksheet name then it will produce the Subscript out of Range error. I did not put an error trap there because the code is written to only run once against the other workbook, per your example scenario in the OP.