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

[WxShady13]

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]

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

 

[My Aswer Is This]

Try something like this:

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]

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

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]

Thank you both, these are awesome

 

[WxShady13]

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

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]

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]

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

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]

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.

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]

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.

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