Microsoft Excel Macro Coding Issue

Question

I have a Macro however it doesnt seem to be working. I have a workbook which has multipul worksheets. I basically want to copy cells B1, G1, M94 all to a seperate "Summary" worksheet. Copied Cells to go to A4 B4 and C4 than if there is more A5, B5 and C5 and so on.

The coding i have is below. I have tried to make it so it only did it for one sheet but need it for about 10 sheets all with different names.

Sub SummurizeSheets()
Dim ws As Worksheet

Application.ScreenUpdating = False
Sheets("Summary").Activate

For Each ws In Worksheets
    If ws.Name <> "17B CUNNINGHAM" Then
        ws.Range("B1, G1, M94").Copy
        Worksheets("Summary").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) _
            .PasteSpecial (xlPasteValues)
    End If
Next ws
End Sub

Simon1979 · Accepted Answer · 2014-02-18 23:30:29Z

1

The problem you will have is you cannot copy/ paste a range the way you have tried to (multiple sections).This should work:

Sub SummurizeSheets()
Dim ws As Worksheet, wsSummary As Worksheet
Dim c As Range

Application.ScreenUpdating = False
Set wsSummary = Sheets("Summary")
' Set destination cell
Set c = wsSummary.Range("A4")

For Each ws In Worksheets
    If ws.Name <> "17B CUNNINGHAM" And ws.Name <> "Summary" Then
        ws.Range("B1").Copy
        c.PasteSpecial (xlPasteValues)
        ws.Range("G1").Copy
        c.Offset(0, 1).PasteSpecial (xlPasteValues)
        ws.Range("M94").Copy
        c.Offset(0, 2).PasteSpecial (xlPasteValues)
        ' Move destination cell one row down
        Set c = c.Offset(1, 0)
    End If

Next ws

Application.ScreenUpdating = True
End Sub

I have used a destination cell to place the paste which you can then offset for the next row so you can use this for multiple sheets. Also excluded the Summary sheet from the For Each and reset the ScreenUpdating

answered Feb 18, 2014 at 23:30

Simon1979

2,1081 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aussiehelper Over a year ago

This worked perfect. Your a champion Thank you so much

Collectives™ on Stack Overflow

Microsoft Excel Macro Coding Issue

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related