Copy multiple worksheets into one

[Busymanjohn]

Hi, I have a piece of code, which was given to me by another person ( I am useless at VBA right now ) .... the code needs adjusting to suit what I need it to do ..... I have a spreadsheet with multiple tabs each containing data, I want to be able to import the data from each worksheet into a front tab ( called Summary ) but I only want to pull the data from those other worksheets if column A has data in it ( column A in each tab contains an IF statement running off of col I ), there will be rows within column A that are blank, I want to ignore those and have the Summary tab populate. here is the code I have just now ....

Sub MoveData()
i = 1
With Worksheets("Summary")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> .Name Then
MyValue = ws.Range("A1")
.Cells(i, "A") = MyValue
i = i + 1
End If
Next
End With
End Sub

Any ideas?

 

[wigi]

For instance:

Sub MoveData()
    i = 1
    With Worksheets("Summary")
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> .Name Then
                For l = 1 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                    If Len(ws.Range("A" & l).Value) > 0 Then
                        i = i + 1
                        .Range("A" & i).Value = ws.Range("A" & l)
                    End If
                Next
            End If
        Next
    End With
End Sub

 

[Busymanjohn]

hey Wigi, thanks for the reply, doesn't give me what I am looking for though, let me see if I can explain it a little more ( and maybe the code I have isn't good enough either ).... the worksheets that contain data range from Column A thru Z and from rows 1 thru 300 ( although this may vary between each worksheet ), column A contains country codes based on an IF statement on what data is in column I, some rows in col A will be blank due to the IF statement, I would like to pull the data from each tab ( cols A - Z ) where col A is populated ( i.e. not blank ) and have that data show in the Summary tab and leaving no blank rows between the data from each worksheet .... e.g.

A B C D E F G H
1 ET 21 14 16 15 12 11 10
2 ET 22 13 17 13 11 9 8
3 ET 41 16 13 12 54 12 13
4 AB 21 15 12 10 11 11 11

In the worksheet called ET there are 5 rows of data, only three have col A populated ( due to the IF statement ), so I only want those imported to the Summary tab, the next worksheet called AB has many rows of data, and again I only want to pull the data where col A is populated and to have AB start directly after ET ,, and continue on through all 18 worksheets .... does that help any?

 

[wigi]

Try this...

Sub MoveData()
    i = 1
    With Worksheets("Summary")
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> .Name Then
                For l = 1 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                    If Len(ws.Range("A" & l).Text) > 0 Then
                        i = i + 1
                        .Range("A" & i).Resize(,26).Value = ws.Range("A" & l).Resize(,26).Value
                    End If
                Next
            End If
        Next
    End With
End Sub

 

[Busymanjohn]

Hi Wigi, works a treat ,, one last thing, the code copies the headings from all the worksheets ( row 1 ), can you adjust the code to start on row 2 and populate on row 2 on the Summary tab ,, that way I can put the headings in once myself and not have them appear throughout the summary .....e.g.

A B C D
1 prod cost sale unit ......these would be the headings
2 ET 12 15 23
3 ET 10 13 45
4 AB 5 7 100 ..... no headings in between ET and AB

 

[wigi]

For l = 2 To ...

 

[Busymanjohn]

Thanks Wigi ,, great code, appreciate the help ( maybe I should get on a course or get a book on VBA to help me in the future ).

 

[wigi]

maybe I should get on a course or get a book on VBA to help me in the future.

That's always a good idea