VBA Copy/Paste from one Sheet to All Others

0
votes

I am trying to copy one cell (D1) of sheet1 to a cell (D1) of all the workbook's other sheets (I am looping through files here and the number of worksheet varies).

When running the code below, the line "ActiveSheet.Paste" gives me the following error: "Run-time error '10004': Paste method of Worksheet class failed".

Here is the problematic piece of code:

'copy MSA code to sheets!=1
Sub MSAallSheets(wb As Workbook)
    With wb
    Range("D1").Copy
        For Each ws In wb.Worksheets
            If ws.Name <> "Page 1" Then
            ws.Activate
            ws.Range("D1").Select
            ActiveSheet.Paste
            End If
        Next
    End With
End Sub

In case it might be necessary, here is how I defined my loop through files:

Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = "C:\Users\julia.anderson\Documents\HMDA\test\"
Filename = Dir(Pathname & "*.xlsx")
Do While Filename <> ""
Set wb = Workbooks.Open(Pathname & Filename)
DoWork wb
Delete wb
MSAallSheets wb
wb.Close SaveChanges:=True
Filename = Dir()
Loop
End Sub

Suggestions would be most welcome!

Thank you.

2
vbaexcel
Welcome to SO! Just a tip: the With wb block is useless and confusing, you're not using the object variable you're Withing with, removing it would only improve readability. And feel free to fix your indentation, I tried to edit it but my edit was rejected because "This edit is incorrect or an attempt to reply to or comment on the existing post.", says an evil unicorn that really knows his stuff. So I guess I shall leave a comment then... – Mathieu Guindon

2 Answers

0
votes

I'm guessing which sheet you're copying from...

Sub MSAallSheets(wb As Workbook)
    With wb
    Range("D1").Copy
        For Each ws In wb.Worksheets
            If ws.Name <> "Page 1" Then
                wb.Sheets("Page 1").Range("D1").Copy _
                                      ws.Range("D1")                      
            End If
        Next
    End With
End Sub
0
votes

This works for me with a slight alteration:

Sub MSAallSheets(wb As Workbook, SourceSheet As String, SourceAddress As String)
    With wb
    Sheets(SourceSheet).Range(SourceAddress).Copy
        For Each ws In wb.Worksheets
            If ws.Name <> SourceSheet Then
                ws.Activate
                ws.Range(SourceAddress).Select
                ActiveSheet.Paste
            End If
        Next
    End With
End Sub

example call:

call MSAallSheets(activeWorkbook, "Page 1", "D1")

The parameters make it easier to change minor details / reuse the code.