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.
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
Okay how is the best way to accomplish that? I appreciate all the help as I am a relative new comer to this level of VBA
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,557
Office Version
  1. 2013
Platform
  1. Windows
Try something like this:
VBA Code:
Sub Add_New_Sheet()
'Modified 12/16/2020  11:07:42 AM  EST
On Error GoTo M
Application.ScreenUpdating = False
Dim ans As String
ans = ActiveSheet.Range("A1").Value

Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
 With Sheets(ans)
 .Cells(1, 5).Value = "Alpha"
 End With
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You may already have a sheet named " & ans
End Sub
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
I believe this should work. You'll still need to change the worksheet name for the original worksheet. I'm guessing it's the worksheet Key

VBA Code:
Sub CopySheets_2()



Dim wsSource As Worksheet

Dim wsNewSht As Worksheet

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



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

Set tbl = Sheets("Original WorkSheet Name").ListObjects("Master")
LastRow = tbl.Range.Rows.Count ' get number of rows in "Master" object



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"



wsName = ActiveSheet.Name



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

WxShady13

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

ADVERTISEMENT

I believe this should work. You'll still need to change the worksheet name for the original worksheet. I'm guessing it's the worksheet Key

VBA Code:
Sub CopySheets_2()



Dim wsSource As Worksheet

Dim wsNewSht As Worksheet

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



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

Set tbl = Sheets("Original WorkSheet Name").ListObjects("Master")
LastRow = tbl.Range.Rows.Count ' get number of rows in "Master" object



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"



wsName = ActiveSheet.Name



tbl.Range(LastRow).Copy
Sheets(wsName).Range("A2").PasteSpecial Paste:=xlValues
End Sub
That is copying the worksheet named "Key" but no pulling the last row from the table named "Master"
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
Can I see how you modified the code? The code worked for me in a trial of just pulling data from a table and pasting it into A2 of a different sheet. Trying to figure out why it's not doing the same for you. T
 

WxShady13

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

ADVERTISEMENT

Can I see how you modified the code? The code worked for me in a trial of just pulling data from a table and pasting it into A2 of a different sheet. Trying to figure out why it's not doing the same for you. T
VBA Code:
Sub CopySheets_2()



Dim wsSource As Worksheet

Dim wsNewSht As Worksheet

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



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

Set tbl = Sheets("Master").ListObjects("Master")
LastRow = tbl.Range.Rows.Count ' get number of rows in "Master" object



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("A2").ListObject.Name = "MyNewName"



wsName = ActiveSheet.Name



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

The last row of data that needs to populate the table EmpData (on the newly created worksheet) will come from the table Master on the page named Master
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
When I have this as the code it works pasting it into a table and not into a table. I can't really try it with the rest of the code. Sorry, I wish I could be more help. If the macro that you originally had ends with the new worksheet being the active sheet you should be able to call this macro at the end. Best workaround I can do.
VBA Code:
Sub CopySheets_2()
Dim tbl As ListObject
Dim LastRow As Long
Dim wsName As String

Set tbl = Sheets("Master").ListObjects("Master")
LastRow = tbl.Range.Rows.Count ' get number of rows in "Master" object
wsName = ActiveSheet.Name

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

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
When I have this as the code it works pasting it into a table and not into a table. I can't really try it with the rest of the code. Sorry, I wish I could be more help. If the macro that you originally had ends with the new worksheet being the active sheet you should be able to call this macro at the end. Best workaround I can do.
VBA Code:
Sub CopySheets_2()
Dim tbl As ListObject
Dim LastRow As Long
Dim wsName As String

Set tbl = Sheets("Master").ListObjects("Master")
LastRow = tbl.Range.Rows.Count ' get number of rows in "Master" object
wsName = ActiveSheet.Name

tbl.Range(LastRow).Copy
Sheets(wsName).Range("A2").PasteSpecial Paste:=xlValues
End Sub
Thank you for getting me this far. I will continue to work on it. I truly appreciate all your help
 

Watch MrExcel Video

Forum statistics

Threads
1,129,518
Messages
5,636,806
Members
416,941
Latest member
shazzaxyz

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