Runtime Error 1004

[cristovam4]

Hi Friends,


I´ve searching all across Google but haven´t found a solution yet.
I have a workbook with a number of sheets (my projects). They increase and decrease based on my business demands.
I´m trying to consolidate 1 column of data for each sheet in one single sheet, in the same column, one after another.
When i run the macro I get the error 1004. What I´m doing wrong?

[COLOR=#2E8B57][FONT=Monaco]Sub test1()[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Dim ws As Worksheet[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]For Each ws In Worksheets[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]If ws.Name <> "Dashboard" Then[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]ws.Select[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]ws.Application.Run "consolidate"[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]End If[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Next[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]End Sub[/FONT][/COLOR]

[COLOR=#2E8B57][FONT=Monaco]Sub consolidate()[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Range(Range("H2"), Range("H2").End(xlDown)).Select[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Selection.Copy[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Sheets("Dashboard").Select[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Range("A1048576").End(xlUp).Offset(1, 0).Select[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Selection.PasteSpecial[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#2E8B57][FONT=Monaco]End Sub[/FONT][/COLOR]

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] test1()
    [color=darkblue]Dim[/color] ws     [color=darkblue]As[/color] Worksheet
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] Worksheets
        [color=darkblue]If[/color] ws.Name <> "Dashboard" [color=darkblue]Then[/color]
            ws.Range("H2", ws.Range("H" & Rows.Count).End(xlUp)).Copy
            Sheets("Dashboard").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color]
    Application.CutCopyMode = [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[cristovam4]

Worked as a charm!
Very, very thanks AlphaFrog.

Try this...

[COLOR=darkblue]Sub[/COLOR] test1()
    [COLOR=darkblue]Dim[/COLOR] ws     [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]If[/COLOR] ws.Name <> "Dashboard" [COLOR=darkblue]Then[/COLOR]
            ws.Range("H2", ws.Range("H" & Rows.Count).End(xlUp)).Copy
            Sheets("Dashboard").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR]
    Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]