Forum Discussion
Automatically paste cell range when a new worksheet is created
I have a group (range A2:U74)of formatted cells and I want that each time a new sheet is created this group of cells to be pasted, the the user can fill the "table" with values.
https://i.imgur.com/sQ88vG1.png
I tried this way:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Worksheets("Sheet1").Range("A277:U349").Copy _
Destination:=Sh.Range("A2")
End Sub
But it doesn't work. However, even if this worked there is a issue: What happens if the user deletes the Sheet1? Where will I copy the range from?
Is there some way to somehow store this range of cells internally to VBA or use a reference object to the Sheet1, not the actual name of the sheet.
Because the user may change the name of Sheet1 and my script will be invalid again.
Can you help me with this?
1 Reply
- PascalKTeamIron Contributor
Hi x12356
Try this code:
Sheets("Sheet1").Range("A2:U74").Copy
Sh.Range("A2").Select
ActiveSheet.PasteYou could hide and protect Sheet1 to make sure that the user can't delete it