Copying last row in a table and pasting it into a new worksheet

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have a workbook that has a macro that will create a new worksheet that houses 3 tables. One of the tables has information about the employee that the worksheet is assigned to. There is a Master table where the employees information is entered (those columns are the same columns on the individual worksheet). I am not sure how to write the code to copy the last row of the master table (Master) to row A2 on the new worksheet. I wont know the new worksheets name as it is being created while also pasting the employee information.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
This should find the last row of the table and copy it to A2.

Option Explicit
Sub CopyData1()

Dim tbl As ListObject
Dim LastRow As Long
Dim wsName as String

Set tbl = Sheets("Original WorkSheet Name").ListObjects("Master")
LastRow = tbl.Range.Rows.Count ' get number of rows in "Master" object
wsName = Range(“Where ever your range for the new worksheet is created”).Text


tbl.Range(LastRow).Copy
Sheets(wsName).Range("A2").PasteSpecial Paste:=xlValues
End Sub
[/CODE]
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Question...for the Range I wont know the new worksheet name until after it is created.
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
is the worksheet name created from a range in the existing worksheet?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Question...for the Range I wont know the new worksheet name until after it is created.
If creating the new sheet is part of this script show us the entire script.
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Ultimately it will take the Employees Last Name, space, Their Employee #. These are all fields on the Master Table
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Here is the code to create a new worksheet

VBA Code:
Sub CopySheets_2()

    Dim wsSource As Worksheet
    Dim wsNewSht As Worksheet
  
    Set wsSource = Worksheets("Key")     'Edit "Sheet1" to your worksheet name.
  
    Set wsNewSht = Worksheets.Add(After:=Worksheets(2)) 'Assign new sheet to a worksheet variable
    wsNewSht.Name = "NewSheet"      'Rename new sheet
  
    wsSource.Range("EmpData[#All]").Copy Destination:=wsNewSht.Range("A1")
    wsSource.Range("PayData[#All]").Copy Destination:=wsNewSht.Range("A5")
    wsSource.Range("EmpActive[#All]").Copy Destination:=wsNewSht.Range("H5")
    
    wsNewSht.Range("A1").ListObject.Name = "MyNewName"
  
End Sub
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
and is this going in the same macro that creates the new worksheet?
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Yes. It will be a callout in the Module but yes it will be activated by the same Macro.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
The way to do this is to have the script create the new sheet and give it the name you want and then continue doing the other part of the script. And refer to that sheet and the name you gave it.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,361
Messages
5,641,674
Members
417,229
Latest member
BODYCOTE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top