Forum Discussion

x12356's avatar
x12356
Copper Contributor
Jan 14, 2020

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

  • PascalKTeam's avatar
    PascalKTeam
    Iron Contributor

    Hi x12356 

     

    Try this code:

     

    Sheets("Sheet1").Range("A2:U74").Copy
    Sh.Range("A2").Select
    ActiveSheet.Paste

     

    You could hide and protect Sheet1 to make sure that the user can't delete it