Importing data without sheetname or reference

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
Hi All,

I'v spent most of the morning trying crack this but I'm at a total loss. I'm trying to make a summary page in a workbook which goes into each sheet, the names of which I don't know and pulls out all of the rows with data in them. I don't know how many rows will be filled in but I am only pulling information from AD&E. Does anyone have any ideas? Here is my code so far....

Code:
Dim LimitA As Long

For Each sh In Worksheets
    If sh.Name <> "Actions" And sh.Name <> "summary" Then
    Worksheets("Actions").Range("B6:B").Value = sh.Range("A2:A" & LimitA).Value
    Worksheets("Actions").Range("C6:C").Value = sh.Range("D2:D" & LimitA).Value
    Worksheets("Actions").Range("D6:D").Value = sh.Range("E2:E" & LimitA).Value
End If
Next sh

My problem is then, how do I get it to jump a line down before it starts to insert data from the next sheet etc when I have specified B6?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Maybe try:

Code:
Dim r as Long
r = 6
For Each sh In Worksheets 
    If sh.Name <> "Actions" And sh.Name <> "summary" Then 
        Worksheets("Actions").Range("B" & r).Value = sh.Range("A2:A" & LimitA).Value 
        Worksheets("Actions").Range("C" & r).Value = sh.Range("D2:D" & LimitA).Value 
        Worksheets("Actions").Range("D" & r).Value = sh.Range("E2:E" & LimitA).Value 
        r = r + 1
    End If 
Next sh
 
Upvote 0
Hi Andrew, thanks for the reply! I've tried the code but its giving me an error. Is it the ("B" & r) do you think because I'm asking it to copy muliple cells from each sheet but then only giving it one cell to copy to? I may be very wrong though :) Do I need to put a ':B' in that bit somewhere?

Jose
 
Upvote 0
I just read my reply and realised my original question was a bit misleading. The LimitA is in there because Im not sure how many row have been filled in. So this macro needs to go into each sheet, find the last row (7 for example) select A2:A7 copy and then paste the cells into this summary sheet from B6 downwards. It then needs to skip a row and start agin with the next sheet!
 
Upvote 0
Sorry I didn't spot that multiple cells were involved. This worked for me:

Code:
Sub Test()
    Const LimitA As Integer = 10
    Dim r As Long
    Dim sh As Worksheet
    r = 6
    For Each sh In Worksheets
        If sh.Name <> "Actions" And sh.Name <> "summary" Then
            Worksheets("Actions").Range("B" & r).Resize(LimitA - 2 + 1).Value = sh.Range("A2:A" & LimitA).Value
            Worksheets("Actions").Range("C" & r).Resize(LimitA - 2 + 1).Value = sh.Range("D2:D" & LimitA).Value
            Worksheets("Actions").Range("D" & r).Resize(LimitA - 2 + 1).Value = sh.Range("E2:E" & LimitA).Value
            r = r + LimitA - 2 + 1
        End If
    Next sh
End Sub
 
Upvote 0
jose001,

This worked for me for varying rows in the copied sheets:

Code:
    Dim sh As Worksheet

    Sheets("Actions").Select
    For Each sh In Worksheets
        If sh.Name <> "Actions" And sh.Name <> "summary" Then
            sh.Range("A2:A" & sh.Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=Worksheets("Actions") _
                .Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
            sh.Range("D2:D" & sh.Cells(Rows.Count, "D").End(xlUp).Row).Copy Destination:=Worksheets("Actions") _
                .Range("C" & Cells(Rows.Count, "C").End(xlUp).Row + 1)
            sh.Range("E2:E" & sh.Cells(Rows.Count, "E").End(xlUp).Row).Copy Destination:=Worksheets("Actions") _
                .Range("D" & Cells(Rows.Count, "D").End(xlUp).Row + 1)
        End If
    Next sh

Have a great day,
Stan
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top