Simple copy sheet VBA code

Paris0022

New Member
Joined
May 17, 2015
Messages
30
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.
 

Some videos you may like

Excel Facts

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,796
Office Version
2013
Platform
Windows
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.

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
Office Version
2013
Platform
Windows
Try this:
Code:
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
Try this:
Code:
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

New Member
Joined
May 17, 2015
Messages
30
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,108
Office Version
365
Platform
Windows
How about
Code:
Sub Paris0022()
   Sheets("Rent Roll").UsedRange.Copy Sheets("Rent Roll Proforma").Range("A1")
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,796
Office Version
2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,401
Messages
5,450,179
Members
405,591
Latest member
Musiclover

This Week's Hot Topics

Top