PDA

View Full Version : How to copy a few cells from each worksheet into a summary sheet?



JohnnyDukey
05-09-2006, 01:22 PM
I have this macro that will copy column A from all existing worksheets into a new worksheet that I am creating call Summary and place each column into its own column. So, if there are 20 worksheets, Summary then has 20 columns....

The one problem that I am having is that I am now being asked to modify this by copying cells A1:A8 and C16:C? (I was just copying the entire column A)into the 1st column of Summary, this will act as a header/guide off to the left.
Then for each worksheet, copy cells B1:B8 and A16:A? and place this in column B
So now if there are 20 worksheets, Summary will have 21 columns..
Confused? I have attached a mock worksheet.

jindon
05-09-2006, 07:49 PM
Hi
try


Sub test()
Dim wsLayout As Worksheet, myCol As Long
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Macro Layout").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set wsLayout = Sheets.Add(Sheets(1))
wsLayout.Name = "Macro Layout"
myCol = 2
For i = 1 To 6
With Sheets("A" & i)
If i = 1 Then
.Range("a1:a8").Copy wsLayout.Range("a1")
.Range("c15", .Range("c" & Rows.Count).End(xlUp)).Copy _
wsLayout.Cells(10, "a")
End If
.Range("b1:b8").Copy wsLayout.Cells(1, myCol)
.Range("a15", .Range("a" & Rows.Count).End(xlUp)).Copy _
wsLayout.Cells(10, myCol)
myCol = myCol + 1
End With
Next
wsLayout.UsedRange.EntireColumn.AutoFit
Set wsLayout = Nothing
End Sub

JohnnyDukey
05-10-2006, 05:14 AM
I see where you are going with this and I'll try to build on it.
The one issue is that the sheet names may or may not follow this convention..A1, A2, A3...they could be any number of things...

Again, Thank you for the post...I'll reply back with my progress.

Thanks!

JohnnyDukey
05-11-2006, 11:20 AM
here is what I did..just a slight modification of the previous post.

Thanks for the replies!!!



For Each sh In Worksheets
i = sh.Index - 1
If sh.UsedRange.Count > 1 Then
Last = Lastcol(myWS)
With Sheets(Worksheets.Item(i + 1).Name)
If i = 1 Then
.Range("A1:A8").Copy myWS.Range("A1")
.Range("C15", .Range("C" & Rows.Count).End(xlUp)).Copy _
myWS.Cells(10, "A")
End If
.Range("B1:B8").Copy myWS.Cells(1, myCol)
.Range("A15", .Range("A" & Rows.Count).End(xlUp)).Copy _
myWS.Cells(10, myCol)
myCol = myCol + 1
myWS.Columns(Last + 1).Columns.AutoFit
End With
End If
Next

Cyberdude
05-11-2006, 12:09 PM
I'm not sure why you are using a macro to do the copying. Is it convenient to just link the cells of the Summary sheet to the cells on the other sheets?

For example, on your Summary sheet, where you would copy a cell from Sheet 3, write ='Sheet 3'!$A$22. This has the advantage (or disadvantage) of having all your Summary sheet cells constantly showing the latest values on the other sheets. This is the technique I use for my summary sheets. If you want the Summary sheet to become updated only at certain times, then the macro copy method would be better.