Pulling rows from tabs to a "summary" sheet

[Reck]

I have a workbook set-up to track sales information. Each individual tab is
dedicated to a single project, and I set it up so all the critical
information from each project is displayed in a "hidden" range from J2:ZQ2.
I need to pull this range from each project to a "Data Pull" tab so I can
then move the information to Access for analysis. Here's my issue...how can
I pull the range J2:ZQ2 from each sheet in the workbook between the tabs
called "First" and "Last" (which act as bookmarks for the individual project
tabs), and drop them into rows in the "Data Pull" tab? I am unsure how to
write a macro that can adjust to differing numbers of project tabs. At any
one time each workbook can have any varying number of current projects. Any
help would be greatly appreciated! Thanks in advance. (BTW, I have Office
Professional 2007)

 

[Jim Cone]

Sub RoundEmUpMoveEmOut()
Dim N As Long
Dim F As Long
Dim L As Long

F = Sheets("First").Index
L = Sheets("Last").Index

For N = F To L
Sheets(N).Range("J2:ZQ2").Copy Destination:= _
Sheets("Data Pull").Cells(Rows.Count, 1).End(xlUp)(2, 1)
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Reck" <[email protected]>
wrote in message
I have a workbook set-up to track sales information. Each individual tab is
dedicated to a single project, and I set it up so all the critical
information from each project is displayed in a "hidden" range from J2:ZQ2.
I need to pull this range from each project to a "Data Pull" tab so I can
then move the information to Access for analysis. Here's my issue...how can
I pull the range J2:ZQ2 from each sheet in the workbook between the tabs
called "First" and "Last" (which act as bookmarks for the individual project
tabs), and drop them into rows in the "Data Pull" tab? I am unsure how to
write a macro that can adjust to differing numbers of project tabs. At any
one time each workbook can have any varying number of current projects. Any
help would be greatly appreciated! Thanks in advance. (BTW, I have Office
Professional 2007)

 

[Reck]

Thank you Jim. I put that into the macro, but when I try to run it I get the
run time error 1004; Application defined or Object Defined error. When I go
into the debugger it highlights the following lines:

Sheets(N).Range("J2:ZQ2").Copy Destination:= _
Sheets("Data Pull").Cells(Rows.Count, 1).End(xlUp)(2, 1)

I am very new to this type of thing, and am trying to learn as I go, so I'm
probably missing something very very basic. Any ideas?

 

[Jim Cone]

I can't really say, it could be something "different" in xl2007.
I don't have that version to test the code on. The code works
for me when I add sheets First, Last and Data Pull to a workbook.
Of course, I adjusted the range width as versions prior to 07 do not
have a column ZQ.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Reck" <[email protected]>
wrote in message
Thank you Jim. I put that into the macro, but when I try to run it I get the
run time error 1004; Application defined or Object Defined error. When I go
into the debugger it highlights the following lines:

Sheets(N).Range("J2:ZQ2").Copy Destination:= _
Sheets("Data Pull").Cells(Rows.Count, 1).End(xlUp)(2, 1)

I am very new to this type of thing, and am trying to learn as I go, so I'm
probably missing something very very basic. Any ideas?

 

[Jim Cone]

I modified the code to qualify sheets with the workbook name and
Rows.Count with the sheet name..
It is worth a try...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub RoundEmUpMoveEmOut_R1()
Dim N As Long
Dim F As Long
Dim L As Long

F = Sheets("First").Index
L = Sheets("Last").Index

For N = F To L
With ThisWorkbook.Sheets("Data Pull")
ThisWorkbook.Sheets(N).Range("B1:F1").Copy Destination:= _
.Cells(.Rows.Count, 1).End(xlUp)(2, 1)
End With
Next
End Sub
'---------------


"Jim Cone" <[email protected]>
wrote in message
I can't really say, it could be something "different" in xl2007.
I don't have that version to test the code on. The code works
for me when I add sheets First, Last and Data Pull to a workbook.
Of course, I adjusted the range width as versions prior to 07 do not
have a column ZQ.

 

[Reck]

Thanks again for your help! I will give it a try as soon as I put out a
couple "little fires" around the office. I'll let you know how it works out.

 

[Reck]

Jim, I reworked the ranges and it works great...small wrinkle though. Is
there something I can put in there that will "paste values" into the
destination cells? Right now I'm getting the dreaded #REF. Thanks again for
your help.

 

[Jim Cone]

'...
Sub RoundEmUpMoveEmOut_R2()
Dim N As Long
Dim F As Long
Dim L As Long

F = ThisWorkbook.Sheets("First").Index
L = ThisWorkbook.Sheets("Last").Index

For N = F To L
ThisWorkbook.Sheets(N).Range("J2:ZQ2").Copy
With ThisWorkbook.Sheets("Data Pull")
.Cells(.Rows.Count, 1).End(xlUp)(2, 1).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Reck" <[email protected]>
wrote in message
Jim, I reworked the ranges and it works great...small wrinkle though. Is
there something I can put in there that will "paste values" into the
destination cells? Right now I'm getting the dreaded #REF. Thanks again for
your help.

 

[Philip]

Jim,
Your code was exactly what I needed for my own project (adapted as below), and works really well. Thank you.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub PullDataFromEachSheetandPasteToSummarySheet()
Range("A2:M65536").Select
Selection.ClearContents
Range("A2").Select

Dim N As Long
Dim F As Long
Dim L As Long

F = Worksheets("Current Issues").Index
L = ThisWorkbook.Sheets("News and Media").Index

For N = F To L
'Change Range("a4:d38,k4:l38") to suit.
ThisWorkbook.Sheets(N).Range("a4:d38,k4:l38").Copy
'Change ThisWorkbook.Sheets("Your Summary Sheet") to refer to the sheet you want the summary in [and make sure the sheet exists!].
With ThisWorkbook.Sheets("Class Summary")
.Cells(.Rows.Count, 1).End(xlUp)(2, 1).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
Range("A1").Select
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Philip