Dummy Excel
Well-known Member
- Joined
- Sep 21, 2005
- Messages
- 1,004
- Office Version
- 2019
- 2010
- 2007
- Platform
- Windows
Hi All,
I have spreadsheet A and im trying to create a macro where it opens up spreadsheet B copies the worksheets out of spreadsheet B (could be one could be more) into spreadsheet A. Then go back into the spreadsheet B unmerge & copy then go into spreadsheet A and paste the data into my lookup table. Then goes back to spreadsheet B and does the same thing for the other worksheets (if any).Once thats done, close spreadsheet B, then loop and do it again for all the files within the folder.
The section of my code for the loop is below, although I cant get the looping correct for each worksheet in spreadsheet B - Any help will greatly be appreciated.
PS the reason for going back & forth is because in spreadsheet A I need the merged cells, so rather than unmerge copy then go back and merge I thought it would be easier going back and forth!
I have spreadsheet A and im trying to create a macro where it opens up spreadsheet B copies the worksheets out of spreadsheet B (could be one could be more) into spreadsheet A. Then go back into the spreadsheet B unmerge & copy then go into spreadsheet A and paste the data into my lookup table. Then goes back to spreadsheet B and does the same thing for the other worksheets (if any).Once thats done, close spreadsheet B, then loop and do it again for all the files within the folder.
The section of my code for the loop is below, although I cant get the looping correct for each worksheet in spreadsheet B - Any help will greatly be appreciated.
Code:
ChDir strPath
strExtension = Dir(strPath & "*.xlsx")
Do While strExtension <> ""
Set wbOpen = Workbooks.Open(strPath & strExtension)
With wbOpen
StatusReport = ActiveWorkbook.Name
Windows(StatusReport).Activate
For Each ws In Worksheets
ws.Copy after:=Workbooks("Flightplan.xlsx").Sheets(2)
Windows(StatusReport).Activate
ws.Range("C5").Select
ws.Range("C5").UnMerge
ws.Range("C5").Copy
Windows(Flightplan).Activate
Sheets("Lookup Table").Activate
For i = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & i + 1).Value = "" Then
Range("A" & i + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
ws.Range("A1").End(xlDown).Offset(1, 0).Select
ws.Range("A" & i + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next i
Next
Windows(StatusReport).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
strExtension = Dir
Loop
PS the reason for going back & forth is because in spreadsheet A I need the merged cells, so rather than unmerge copy then go back and merge I thought it would be easier going back and forth!