# Importing data without sheetname or reference

#### jose001

##### Board Regular
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Andrew Poulsom

##### MrExcel MVP
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``````

#### jose001

##### Board Regular
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

#### jose001

##### Board Regular
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!

#### Andrew Poulsom

##### MrExcel MVP
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``````

#### stanleydgromjr

##### Banned
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

#### jose001

##### Board Regular
Stanley you are a legend!

Replies
4
Views
423
Replies
2
Views
272
Replies
2
Views
327
Replies
0
Views
1K
Replies
5
Views
867

1,191,534
Messages
5,987,142
Members
440,082
Latest member
belodelokelo

### 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.

### Which adblocker are you using?

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

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