copy range from sheet 1 to all other sheets

[Smithgall]

what i want is to reset a range of cell on all sheets based on sheet1 whcih is a master sheet. I basically have a range from b9:m39 that represents each day in the year. then i have a sheet for each emlployees where they have their ddays off listed and calculated. It also conditionally blacks out Holidays and weekends so visually you can tell what you have.

At the beginning of next year all of those employees will need updated sheets with the new weekend and holiday dates blacked out. What i want to do it to have a command button that allows the admin person to change the master sheet and the reset all the employee sheets range b9:b39 to read the same as master sheet 1 with the new dates. The ranges is the same for each sheet. This can eaither be pasted link or a copy.

Can i copy a range and then paste that same range on all sheets?

 

[Datsmart]

To do this, copy to clipboard the range from the master sheet you want on the other sheets.
Then, Ctrl-click each of the sheets you want the new data to be pasted to.
Click cell B9 of the visible sheet and paste, (Ctrl-V). The date will be pasted to all the selected sheets.
If you want a macro for this, turn on Macro Recorder before you start and you will have your code.

 

[Smithgall]

copy range on multiple shets

That will work but the code that is created indicates the specific sheets. What i need it to do is determine on its own how many sheets are in the workbook and copy it to the end. This is sample code that was created

Sub copy()
'
' copy Macro
' Macro recorded 10/2/2006 by mSmithgall
'

'
Range("B2:D4").Select
Selection.copy
Sheets("Sheet2").Select
Range("B2:D4").Select
Sheets(Array("Sheet2", "Sheet3")).Select
Sheets("Sheet2").Activate
ActiveSheet.Paste
End Sub

 

[Datsmart]

Try this code:

Sub CopyMyRange()
    Dim ws As Worksheet
    Dim CSN As String
    
    CSN = ActiveSheet.Name
    
    'position this worksheet to first position
    Worksheets(CSN).Move Before:=Worksheets(1)
    
    'copy range to each worksheet in workbook
    For Each ws In Worksheets
        Sheets(CSN).Range("B2:D4").Copy Destination:=ws.Range("B2")
    Next ws

End Sub

 

[Smithgall]

works great

thanks man that works great. you are DatSmart!

 

[Datsmart]

Actually, Datsmart is my last name spelled backwards with a T on the end.
But thanks, glad it worked for you.