copy range from sheet 1 to all other sheets

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Board Regular
Joined
May 24, 2006
Messages
68
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Try this code:
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
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Actually, Datsmart is my last name spelled backwards with a T on the end.
But thanks, glad it worked for you.
 

Forum statistics

Threads
1,136,926
Messages
5,678,618
Members
419,776
Latest member
mikelowski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top