Copy and paste data into the same range of multiple worksheets by using a VBA module code

Question

Granted that I'm a newby.

I need to copy a specific cells area ("B6:C36") from a single worksheet (named "FILE MASTER") to all the other worksheets within the same workbook.

After that, I need to assign this brand new macro to a Button existing in the file master worksheet (therefore this macro has to have a name/sub otherwise I cannot assign it to a Button).

Having said that I tried to create a macro by using the recording feature of MS Excel, and it works. But it has a serious weakness: this automatedly encoding process has used/enunciated the name of every single worksheets in the source code. So if I add a new worksheet, this macro won't work correctly anymore.

Hope to have been enough clear

Thank you in advance to everybody.

In your `FILE MASTER` worksheet, does the input range you specified ever change? — mray190, May 21 '19 at 13:40
Did you do any research at all ([ask])? *"VBA loop throug all worksheets"* should give you a thouthands of results in Google which explain how to loop through all the sheets. — Pᴇʜ, May 21 '19 at 13:42
Have a look at this answer - give it a vote if it helps..., see : https://stackoverflow.com/a/30605765/4961700 — Solar Mike, May 21 '19 at 13:53
@mray190 the range to be copied is always the same, thats is the "B6:C36" of FILE MASTER. I forgot to say, I need to copy both data and format from that cells. And just to avoid misunderstanding, I need that this code replace the same range "B6:C36" in each worksheet in the same position "B6:C36" — ValerioS, May 21 '19 at 14:15
@Pᴇʜ I tried to google about 40 times before deciding to ask for a question here. Sorry for bothering you. Any help is welcome :) — ValerioS, May 21 '19 at 14:17
@SolarMike I had a look now, way too complex code for me in order to get mine version. I'm a total newby and even less a programmer — ValerioS, May 21 '19 at 14:21

score 1 · Accepted Answer · answered May 21 '19 at 13:48

1

You could change the code and try the below:

Option Explicit

Sub CopyYes()

    Dim ws As Worksheet

    With ThisWorkbook
        'Copy the range
        .Worksheets("FILE MASTER").Range("B6:C36").Copy

        'Loop sheets
        For Each ws In .Worksheets

            With ws
                'Avoid FILE MASTER
                If .Name <> "FILE MASTER" Then
                    'Paste only values in A1 of each sheet
                    .Range("A1").PasteSpecial xlPasteValues
                End If

            End With

        Next ws

    End With

End Sub

answered May 21 '19 at 13:48

Error 1004

7,877
3
23
46

"Run-time error '9' --> Subscript out of range" I get this error by using the code above – ValerioS May 21 '19 at 14:11
@ValerioS Check your sheet name make sure a sheet `FILE MASTER` does exist and that there are no additional spaces in its name. – Pᴇʜ May 21 '19 at 14:20
@Pᴇʜ You were right!!!! There was a space after FILE MASTER. One last thing: I have changed the second part of the code suggested by Error 1004, that is range "A1" in "B6:C36". Now it works but it copies only the value of the cells, not the format. – ValerioS May 21 '19 at 14:34
@ValerioS look into the documentation of the [Range.PasteSpecial method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial). You will find out what the [XlPasteType enumeration](https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype) is and `xlPasteValues` means that only values get pasted (no formattings). • The solution for your problem is in the documentation. – Pᴇʜ May 21 '19 at 14:37

ValerioS · Answer 2 · 2019-05-21T15:40:38.267

0

Option Explicit

Sub CopyYes()

Dim ws As Worksheet

With ThisWorkbook
    'Copy the range
    .Worksheets("FILE MASTER").Range("B6:C36").Copy

    'Loop sheets
    For Each ws In .Worksheets

        With ws
            'Avoid FILE MASTER
            If .Name <> "FILE MASTER" Then
                'Paste values and formats in B6:C36 of each sheet
                .Range("B6:C36").PasteSpecial xlPasteValues
                .Range("B6:C36").PasteSpecial xlPasteFormats
            End If

        End With

    Next ws

End With

End Sub

edited May 21 '19 at 15:40

answered May 21 '19 at 14:42

ValerioS

13
4

1

I share here the modified code. Now it works. Thank you to all!! :)) In particular to Error 1004 and Pᴇʜ – ValerioS May 21 '19 at 14:45
1

Please note that according to the documentation it must be `xlPasteValues` and `xlPasteFormats`. – Pᴇʜ May 21 '19 at 15:09

Copy and paste data into the same range of multiple worksheets by using a VBA module code

2 Answers2