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

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
184
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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]
 
Upvote 0
Question...for the Range I wont know the new worksheet name until after it is created.
 
Upvote 0
Ultimately it will take the Employees Last Name, space, Their Employee #. These are all fields on the Master Table
 
Upvote 0
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
 
Upvote 0
Yes. It will be a callout in the Module but yes it will be activated by the same Macro.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top