Summarizing Budgets

[wootenka]

I have a workbook with 30+ worksheets, one for each reporting center. I am trying to create code that will copy the full year numbers (column CD) into one summary worksheet. Each reporting center would be a column in the summary worksheet

Below is the current code. It runs and goes to the summary sheet but does not paste anything. If I hit enter, it paste random colors that I do not think are anywhere in my workbook,

Sub SummurizeSheets()
Dim ws As Worksheet

Application.ScreenUpdating = False
Sheets("Summary").Activate

For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Range("cd1").EntireColumn.Copy
ActiveSheet.Range("b1").End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues
End If
Next ws
End Sub

 

[alansidman]

try this:

Option Explicit


Sub SummurizeSheets()
    Dim ws As Worksheet
    Dim lr As Long, l2 As Long


    Application.ScreenUpdating = False
    Sheets("Summary").Activate


    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            lr = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row + 1
            l2 = ws.Range("CD" & Rows.Count).End(xlUp).Row
            ws.Range("CD1:CD" & l2).Copy
            Sheets("Summary").Range("B" & lr).PasteSpecial xlPasteValues
        End If
    Next ws
End Sub

 

[wootenka]

Thank you. I get a Compile Error: Ambiguous name detected in the "lr = ActiveSheet.Range("B" & rows.Count).End(xlUp).Row + 1" section with rows highlighted.

Any thoughts?