Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Simple copy sheet VBA code

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Simple copy sheet VBA code

    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.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,846
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Simple copy sheet VBA code

    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 by JLGWhiz; Apr 20th, 2019 at 03:04 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,218
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Simple copy sheet VBA code

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  4. #4
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,253
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple copy sheet VBA code

    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
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  5. #5
    New Member
    Join Date
    May 2015
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple copy sheet VBA code

    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    21,817
    Post Thanks / Like
    Mentioned
    357 Post(s)
    Tagged
    36 Thread(s)

    Default Re: Simple copy sheet VBA code

    How about
    Code:
    Sub Paris0022()
       Sheets("Rent Roll").UsedRange.Copy Sheets("Rent Roll Proforma").Range("A1")
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,846
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Simple copy sheet VBA code

    Quote Originally Posted by Paris0022 View Post
    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
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •