Hello excel community,
I have a question that I am trying to solve that relates to using Macros and VBAs. I think it will help if I first detail what I am working with and then describe what I am trying to achieve.
Setup
I have two workbooks. Each workbook has multiple sheets. I run an identical sub on each of these workbooks separately. The subs loops through the sheets of each workbook and does some table formatting and names each sheet according to the cell in A1 of that sheet.
The difference between the 'identical' macros is only in regards to some text in certain cells, but they have identical format. The workbooks themselves can but do not necessarily have the same number of sheets.
What I am trying to achieve
Lets call the first workbook wb1 and the the second workbook wb2. wb1 has 5 sheets and wb2 has 3 sheets. The names of the sheets in wb1 are "Sheet 1", Sheet 2", and "Sheet 3". The names of the sheets in wb2 are "Sheet 1", Sheet 2", Sheet 3", Sheet 4", and "Sheet 5". The important detail to note is that sheet names in wb2 will always be a subset of the sheet names in wb1.
What I want to do is the following:
For each sheet in wb1
Find the cell in column A that is 3 rows below the first excel table in the sheet. Each sheet has 2 tables but I want to effectively know what cell position in column A is 3 rows below the last data row of the first table. Example: "Sheet 1" in wb1 has a table whose first column header is in position A3. This means that it has 2 empty rows (Row 1 and Row 2). The tables last data row is in row 9. Therefore, I want a block of code that will "store" position A12. Effectively, "Sheet 1" is paired with cell A12 (in an array?). Every sheet in wb1 should have a stored position. Lets call that position "Paste_Here".
For each sheet in wb2
copy range A4 to M9
paste the copied range xlPasteValuesAndNumberFormats in cell "Paste Here"
In this example, the expected behavior is that Sheet 1 through Sheet 3 of wb2 will get have a range pasted to sheets with the same name in wb2. Notably, Sheet 4 and Sheet 5 of wb2 will not get a pasted range.
Please note that the Sheets in both workbooks are not in an order, so the way the code knows what sheet from wb2 to copy from and what sheet from wb1 to paste to is entirely dependent on the sheets having the same name.
Below is the code I have that gets me to the purpose of this post:
Dim wb1 As Workbook
Dim wb2 As Workbook
' Helps with performance
Application.ScreenUpdating = False
Application.EnableEvents = False
'Set the reference to the first workbook
Set wb1 = Workbooks.Open("C:\Users\some name 1.xlsm")
'Set the reference to the second workbook
Set wb2 = Workbooks.Open("C:\Users\some name 2.xlsm")
' Call the sub that will prepare the sheets in the first workbook
Prepare_tables_1 wb1
' Call the sub that will prepare the sheets in the second workbook
Prepare_tables_1 wb2
' This is where your suggestions will go!!
' TODO
' TODO
' Turn it back on
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
