Smurphy820
New Member
- Joined
- May 25, 2022
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
What I need to do is go through the prior month's workbook and pull data from only the worksheets that have the same name as the worksheets in the current month's workbook. I started out by saving all of the current month's worksheet names into a list. But from there I am kind of stuck on where to go next.
The code above is a snippet of the part that gives me a list of all of the worksheets I want to compare to the prior month's workbook. Below is an example for the worksheet the code above just opened ("PriorDoc"). I need to go through these worksheets and copy the data table starting at cell A6. The table is in the same location every time.
Then I have to paste the data tables from only the worksheets that match into the current month's "ASM main" worksheet on top of one another like below. Is this possible? Thanks!
VBA Code:
For Each ws In ActiveWorkbook.Worksheets
If (UCase(ws.Name) Like "ASM*") And (UCase(ws.Name) <> "ASM MAIN") Then
Sheettitle1 = ws.Name
SheettitleArray = Split(Sheettitle1, "_")
Sheettitle = SheettitleArray(1)
ws.Range("E23:E32").Formula = "=VLOOKUP($B23, '[" & Name & "]" & Sheettitle & "'!$A$3:$E$261,3,FALSE)"
ws.Range("F23:F32").Formula = "=VLOOKUP($B23, '[" & Name & "]" & Sheettitle & "'!$A$3:$E$261,4,FALSE)"
ws.Range("G23:G32").Formula = "=VLOOKUP($B23, '[" & Name & "]" & Sheettitle & "'!$A$3:$E$261,5,FALSE)"
Sheets("ASM Main").Cells(Rows.Count, "A").End(xlUp).Offset(2, 0) = ws.Name
'
ws.Range("A6").CurrentRegion.Copy
Worksheets("ASM Main").Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
Worksheets("Macros").Select
Range("X7").CurrentRegion.Select
Selection.Copy
Worksheets("ASM Main").Select
Cells(Rows.Count, "A").End(xlUp).Offset(-12, 20).Select
ActiveSheet.Paste
List(x) = ws.Name ' creates the list
x = x + 1
End If
Next ws
folderpath = "C:\Users\XXXX\XXX\Documents\XXXX\XXXX\" & newDate & "\" ' open the prior months excel
sFound = Dir(folderpath & "*PriorDoc.xlsx")
If sFound <> "" Then
Workbooks.Open Filename:=folderpath & "\" & sFound
Name = ActiveWorkbook.Name
End If
The code above is a snippet of the part that gives me a list of all of the worksheets I want to compare to the prior month's workbook. Below is an example for the worksheet the code above just opened ("PriorDoc"). I need to go through these worksheets and copy the data table starting at cell A6. The table is in the same location every time.
Then I have to paste the data tables from only the worksheets that match into the current month's "ASM main" worksheet on top of one another like below. Is this possible? Thanks!