looping worksheets

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
1,003
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. 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.



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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
1,003
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
bump?

Do I need to try and explain it differently?
All im trying to do is copy a worksheet from ss A to ss B, then go back to ss A and copy a few cells. If there are mutilple worksheets do it for all worksheets.
Ive got the loop for coping all the worksheets, although I cant get the coping working?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,485
Messages
5,831,962
Members
430,098
Latest member
bemmelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top