Macro Query

C

carla 7

Newbie here, please help with the following: I have a workbook with all
worksheets formatted the same. I am in need of a macro that will copy the
value of a cell, say M6, in each M6 cell from the 80 worksheets, and then
paste it to the last sheet in the workbook. However, the cells in the last
worksheet should be pasted one cell down consecutivively.

Greatly appreciate your help
 
J

Jarek Kujawa

try this macro

Sub kopiuj()

For Each Worksheet In ActiveWorkbook.Worksheets
i = i + 1
If i < ActiveWorkbook.Worksheets.Count Then
Worksheets(i).Range("M6").Copy
Worksheets(ActiveWorkbook.Worksheets.Count).Cells(i, 1).PasteSpecial
Paste:=xlValues
End If
Next

End Sub

pastes values from M6 in any worksheet (but the last one) to the last
one, starting from A1

adjust yr ranges as necessary
 
C

carla 7

Thanks for your reply, I am getting a compile error:syntax error message for
line:
Paste:=xlValues. But the macro seems to be the right one except for the
syntax error.
 
D

Don Guillett

try this.

Sub copyeachshtcelltosummary()
ds = Sheets(Sheets.Count).Name
For i = 1 To Sheets.Count - 1
Sheets(i).Range("m6").Copy Sheets(ds).Cells(i, "a")
Next i
End Sub
 
C

carla 7

Waw! you guys are amazing....

Don Guillett said:
try this.

Sub copyeachshtcelltosummary()
ds = Sheets(Sheets.Count).Name
For i = 1 To Sheets.Count - 1
Sheets(i).Range("m6").Copy Sheets(ds).Cells(i, "a")
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Click to expand...
 
C

carla 7

Hello Jarek,

Fast question. Instead of the macro pasting to the values to the last
worksheet vertically, is there anyway it can paste the values horizontally?

Please Help
 
Top