Simple copy sheet VBA code

[Paris0022]

I have a sheet called “Rent Roll”. I am looking for a simple Macro VBA code to copy the Rent Roll data sheet into a new sheet called Rent Roll Proforma.

Basically I would like to have a format control button linked to a macro and when clicked, it will copy the data in the Rent Roll tab into my new sheet.

Thanks for your help.

 

[JLGWhiz]

You can put a button on the worksheet after the code is installed in code module1 by clicking Developer on the Ribbon, then click Insert. Left click the button icon in the Form Controls tool box, then move the cross hairs (+) to where you want the button and click again. A pop up menu will display from which you can click 'Attach a Macro' and use the dialog box that appears to select the macro you want to attach to the button.

Sub copyRentRoll()
Sheets("Rent Roll").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Rent Roll Proforma"
End Sub

 

[My Aswer Is This]

Try this:

Sub Copy_My_Sheet()
'Modified  4/20/2019  3:08:15 PM  EDT
Sheets("Rent Roll").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Rent Roll Proforma"
End Sub

 

[bobsan42]

Try this:

Sub copier()
    Dim wsh As Worksheet, wsh2 As Worksheet
    Set wsh = ThisWorkbook.Worksheets("Rent Roll")
    wsh.Copy , wsh
    Set wsh2 = ThisWorkbook.ActiveSheet
    wsh2.Name = "Rent Roll Proforma"
    wsh2.UsedRange.Formula = wsh.UsedRange.Value
    Set wsh = Nothing
    Set wsh2 = Nothing
End Sub

 

[Paris0022]

Thank you for your replies. What if I already have a Rent Roll Pro forma Tab already created. I have a button with a macro and it will say, copy Rent Roll sheet into this sheet.

What code would that be? I see the code you give me will be creating a sheet. But I already have the sheet created and just want it copied over.

 

[Fluff]

How about

Sub Paris0022()
   Sheets("Rent Roll").UsedRange.Copy Sheets("Rent Roll Proforma").Range("A1")
End Sub

 

[JLGWhiz]

Thank you for your replies. What if I already have a Rent Roll Pro forma Tab already created. I have a button with a macro and it will say, copy Rent Roll sheet into this sheet.

What code would that be? I see the code you give me will be creating a sheet. But I already have the sheet created and just want it copied over.

Be careful how you word your posts. "New" means it does not currently exist.

copy the Rent Roll data sheet into a new sheet called Rent Roll Proforma