looping worksheets

Dummy Excel

Well-known Member
Sep 21, 2005
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.

ChDir strPath
strExtension = Dir(strPath & "*.xlsx")

        Do While strExtension <> ""
            Set wbOpen = Workbooks.Open(strPath & strExtension)
            With wbOpen
                StatusReport = ActiveWorkbook.Name
            For Each ws In Worksheets
                    ws.Copy after:=Workbooks("Flightplan.xlsx").Sheets(2)
                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
                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
                Application.DisplayAlerts = False
                Application.DisplayAlerts = True
            End With
            strExtension = Dir

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!

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Dummy Excel

Well-known Member
Sep 21, 2005

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?

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...