Macro for copying data from First sheet to all the other sheets

[beautify5]

Hello Experts,

I want to copy a range (a1:d6) from Sheet1 (Name changes everytime) to to the same cells (a1:d6) of all other sheets in the workbook (the number of sheets changes everytime).

Thanks in advance.

 

[Domenic]

Try something like this...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] CopyToOtherSheets()

    [COLOR=darkblue]Dim[/COLOR] Wks                 [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] wsSource            [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rSource             [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]Set[/COLOR] wsSource = Worksheets(1) [COLOR=green]'first sheet[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rSource = wsSource.Range("A1:D6") [COLOR=green]'copy range[/COLOR]
    
    [COLOR=green]'copy to other sheets[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Wks [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]If[/COLOR] Wks.Name <> wsSource.Name [COLOR=darkblue]Then[/COLOR]
            rSource.Copy Wks.Range(rSource.Address)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] Wks
    
    MsgBox "Completed...", vbInformation
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[beautify5]

Thanks Domenic. Let me try

 

[beautify5]

Try something like this...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] CopyToOtherSheets()

    [COLOR=darkblue]Dim[/COLOR] Wks                 [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] wsSource            [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rSource             [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]Set[/COLOR] wsSource = Worksheets(1) [COLOR=green]'first sheet[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rSource = wsSource.Range("A1:D6") [COLOR=green]'copy range[/COLOR]
    
    [COLOR=green]'copy to other sheets[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Wks [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]If[/COLOR] Wks.Name <> wsSource.Name [COLOR=darkblue]Then[/COLOR]
            rSource.Copy Wks.Range(rSource.Address)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] Wks
    
    MsgBox "Completed...", vbInformation
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

5 hours i tried and it didn't work, and a help from a Genius solved it in 5 min. Thank you so much bro.

 

[Domenic]

You're very welcome! And thanks for the kind words!

Cheers!