VBA Not Running

[dawnmichelle]

I am using the below to consolidate several worksheets into a summary sheet within the same workbook. It was running fine until I saved and reopened the workbook and I have no clue why...... I did save it as a macro-enabled workbook and my security settings are correct. I have "debugged" and it runs through each line without generating an error. When I run the macro, it appears to be running through it but it is not actually doing anything (pulling data into the summary sheet). All thoughts are appreciated.

Public Sub combine()
Dim ws  As Worksheet, _
    LR1 As Long, _
    LR2 As Long
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Summary" And ws.Name <> "MasterJE" And ws.Name <> "JE-B728" And ws.Name <> "JE-B545" And ws.Name <> "JE-B542" And ws.Name <> "JE-B541" And ws.Name <> "JE-B363" And ws.Name <> "JE-B225" And ws.Name <> "MasterIC" And ws.Name <> "JE Pivot" Then
        LR1 = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row + 1
        LR2 = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("A7:L" & LR2).Copy Destination:=Sheets("Summary").Range("A" & LR1)
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[rollis13]

I'm not sure, but if I were you I would, first of all, check the spelling (or extra spaces) and/or case of the tab names.

 

[Michael M]

And make sure sheets aren't protected

 

[Skyybot]

Do you have any other Workbooks open?

 

[bebo021999]

Try to use Debug.Print to test within the code:

If ws.Name <> "Summary" And ws.Name <> "MasterJE" And ws.Name <> "JE-B728" And ws.Name <> "JE-B545" And ws.Name <> "JE-B542" And ws.Name <> "JE-B541" And ws.Name <> "JE-B363" And ws.Name <> "JE-B225" And ws.Name <> "MasterIC" And ws.Name <> "JE Pivot" Then
debug.print ws.name

Then Ctrl-G to open Intermediate Window to see if the ws.name was printed out...

 

[dawnmichelle]

Try to use Debug.Print to test within the code:

If ws.Name <> "Summary" And ws.Name <> "MasterJE" And ws.Name <> "JE-B728" And ws.Name <> "JE-B545" And ws.Name <> "JE-B542" And ws.Name <> "JE-B541" And ws.Name <> "JE-B363" And ws.Name <> "JE-B225" And ws.Name <> "MasterIC" And ws.Name <> "JE Pivot" Then
debug.print ws.name

Then Ctrl-G to open Intermediate Window to see if the ws.name was printed out...

there is no output at all, which is the issue; it was working fine when i closed the sheet for the day............the next morning i tried to run it and nothing!

 

[dawnmichelle]

Do you have any other Workbooks open?

I do not

 

[Skyybot]

I do not

Then try changing

For Each ws In ActiveWorkbook.Worksheets

to

For Each ws In ThisWorkbook.Sheets

 

[dawnmichelle]

Then try changing

For Each ws In ActiveWorkbook.Worksheets

to

For Each ws In ThisWorkbook.Sheets

Nope. Still doesn't pull any data

 

[dmt32]

Hi
untested but see if this update to your code resolves your issue

Option Explicit
Public Sub combine()
    Dim ws           As Worksheet, wsSummary As Worksheet
    Dim LR(1 To 2)   As Long
    Dim IgnoreSheets As Variant
    
    Set wsSummary = ThisWorkbook.Worksheets("Summary")
    
    IgnoreSheets = Array("Summary", "MasterJE", "JE-B728", "JE-B545", "JE-B542", _
                   "JE-B541", "JE-B363", "JE-B225", "MasterIC", "JE Pivot")
    
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, IgnoreSheets, 0)) Then
            LR(1) = wsSummary.Range("A" & wsSummary.Rows.Count).End(xlUp).Row + 1
            LR(2) = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A7:L" & LR(2)).Copy Destination:=wsSummary.Range("A" & LR(1))
        End If
        Erase LR
    Next ws
    Application.ScreenUpdating = True
End Sub

Dave