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

 

[dawnmichelle]

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

it is doing the same thing.............it looks like the macro is running but nothing pulls into the summary tab; here's a screenshot of my tabs just in case i have done something stupid. I am so incredibly frustrated

 

[dmt32]

Should work - Is your summary sheet completely clear i.e. have you any data further down the sheet?
Try scrolling down & see if the data is being posted much lower down.

Dave

 

[dawnmichelle]

Should work - Is your summary sheet completely clear i.e. have you any data further down the sheet?
Try scrolling down & see if the data is being posted much lower down.

Dave

O M G - it is starting it on line 777,217..............WHY? and what on earth made you think of that?

 

[Skyybot]

What happens when you change

LR1 = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row + 1

to

LR1 = Sheets("Summary").Rows(Sheets("Summary").Rows.Count).End(xlUp).Row + 1

 

[dmt32]

O M G - it is starting it on line 777,217..............WHY? and what on earth made you think of that?

because it is a problem often overlooked when people create a summary sheet - not fully clearing the data to the bottom of the used range before posting new data.

Suggest consider adding new line of code to fully clear the sheet before refreshing with new data

Something like this may work for your requirement

Set wsSummary = ThisWorkbook.Worksheets("Summary")
   
wsSummary.UsedRange.Clear

Dave

 

[dawnmichelle]

Thank you all so much! Back up and running!