Consolidate to Summary Page

[Factor3]

Situation:
I've got different prices on each worksheet [Range("C2:C22")], and I am trying to create a Macro that copies the price column of each worksheet into a "Summary" worksheet where the prices will then be graphed.

This is what I've got:

Sub Summary_Page()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range

Worksheets(1).Activate
Set ws2 = Worksheets("Summary")

For Each ws1 In ActiveWorkbook.Worksheets
If Not ws1.Name = ws2.Name Then
'Paste Price Range into Worksheet ("Summary")
For Each Range("C4:C24") In ws1
Range.Copy Destination:=Sheets("Summary").Range("C4:C24").Offset(1, 0):banghead:
End If
Next rng
End If
Next ws1
End Sub

The area highligthed in Red is the one giving me problems. I'm not doing something right. Am I at least on the right track? Or am I way off base?

Thanks,

You guys ROCK

[mdmackillop]

I'm not sure if this is the layout you're after.
Option Explicit
Sub Summary_Page()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim i As Long

Worksheets(1).Activate
Set ws2 = Worksheets("Summary")

For Each ws1 In ActiveWorkbook.Worksheets
If Not ws1.Name = ws2.Name Then
'Paste Price Range into Worksheet ("Summary")
ws1.Range("C4:C24").Copy Sheets("Summary").Range("C4:C24").Offset(, i)
i = i + 1
End If
Next ws1
End Sub

if you want 1 column then
For Each ws1 In ActiveWorkbook.Worksheets
If Not ws1.Name = ws2.Name Then
'Paste Price Range into Worksheet ("Summary")
ws1.Range("C4:C24").Copy Sheets("Summary").Range("C4:C24").Offset(i)
i = i + 21
End If
Next ws1

[Factor3]

That's IT!!! The only problem is that when I run the Macro, the first column that is pasted into in the "Summary" page is D4:D24, not C4:C24.

I'm assuming that is because 'i' is set to equal i + 1 in the For loop, which puts it at Column C+1 (i.e. column D). Where would I initially define i = 0, and then loop it through so that the first column that is pasted into is column 'C' instead of 'D'?

[mdmackillop]

I noticed that and edited my code to put i+1 after the first paste.

[Factor3]

Inside of the If..Then loop?

If Not ws1.Name = ws2.Name Then


'Paste Price Range into Worksheet ("Summary")
ws1.Range("C2:C22").Copy Sheets("Summary").Range("B4:B24").Offset(, i)
i = i + 1
End If

How can you alter it after the first paste?

[Factor3]

Each worksheet of the book is named the price download date (mm.dd.yy). I want to include this in the "Summary" page. I added the line in red in hopes that it would also add to the summary page the download date (i.e. the "Name" of the worksheet the price was downloaded from). What am I missing?

Sub Sum_Page()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim i As Long

Worksheets(1).Activate
Set ws2 = Worksheets("Summary")

For Each ws1 In ActiveWorkbook.Worksheets
If Not ws1.Name = ws2.Name Then
'Paste Price Range into Worksheet ("Summary")
ws1.Name.Value.Copy Sheets("Summary").Range("C3").Offset(, i)
ws1.Range("C2:C22").Copy Sheets("Summary").Range("B4:B24").Offset(, i)
i = i + 1
End If
Next ws1
End Sub

[mdmackillop]

See Post #2. The initial value of i is 0 by default.

[mdmackillop]

You can't Copy the sheet name, only data from cells.
Sub Summary_Page()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim i As Long

Worksheets(1).Activate
Set ws2 = Worksheets("Summary")

With Sheets("Summary")
For Each ws1 In ActiveWorkbook.Worksheets
If Not ws1.Name = ws2.Name Then
'Set title
With .Range("C3").Offset(, i)
.Value = ws1.Name
.Font.Bold = True
End With
'Add data
ws1.Range("C4:C24").Copy .Range("C4:C24").Offset(, i)
i = i + 1
End If
Next ws1
End With
End Sub