copy a table in 1 workbook to another workbook

Question

I am new to vba coding. I do not know if this is possible but can you copy a table (Daily) in 1 of my sheets to another workbook. I want the 2nd workbook to open, paste the selected data range into the table in the 2nd workbook, and then save and close the 2nd workbook.

Any help is much appreciated.

Private Sub CommandButton3_Click()

Dim ws As Sheets
Set ws = Sheet4
Dim tbl As ListObject
Dim dt As Integer
Dim line As String
Dim shift As String
Dim prod As Integer
Dim mydata As Workbook
Dim FileName As String
Dim lastrow As Long


FileName = "C:\Users\john.bauer\Desktop\Archive.xlsx"



Set tbl = ws.ListObjects("Daily")
With tbl
    Range(1).Value = dt
    Range(2).Value = line
    Range(3).Value = shift
    Range(4).Value = prod
End With



Set wb = wookbooks.Open(FileName).wb.woorksheet("Table").Active

lasrow = ActiveSheet.Cells(Row.Count, 1).End(x1up).Rows

Active.Cells(lastrow + 1, 1).Select

dt.Paste
Active.Cells(lastrow + 1, 2).Select
line.Paste
Active.Cells(lastrow + 1, 3).Select
shift.Paste
Active.Cells(lastrow + 1, 4).Select
prod.Paste

ActiveWorkbook.Save
ActiveWorkbook.Close savechanges = True

So what is the problem with your code? Note that when you use `With` statement, You need to add a dot infront to refer to the object e.g. `.Range ...`. And you will benefit from reading on [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1), select/activate is bad practice in general and you should avoid it. — Raymond Wu, Nov 09 '21 at 03:05
it says method or data member not found and highlights this line. — user13596510, Nov 09 '21 at 03:09
Many of these lines are not executable due to incorrect spelling `wookbook` instead of `Workbooks`. Using the number one instead of the letter L in `xlup`, `lasrow` instead of `lastrow`… — Darrell H, Nov 09 '21 at 03:09
`Dim ws as Worksheet`. You will still err on the following lines. — Darrell H, Nov 09 '21 at 03:21

VBasic2008 · Answer 1 · 2021-11-09T06:33:00.970

Bakup Table Data

Adjust the values in the constants section (e.g. the source worksheet name (swsName) was never mentioned).
If you want to copy all columns of the table and it accidentally has only four columns, then you should remove the line Const sfCols As Long = 4 and the .Resize(, sfCols) part.

Standard Module e.g. Module1

Option Explicit

Sub BackupDaily()
    
    ' Constants
    
    ' Source
    Const swsName As String = "Sheet1"
    Const stblName As String = "Daily"
    Const sfCols As Long = 4
    ' Destination
    Const dFilePath As String = "C:\Users\john.bauer\Desktop\Archive.xlsx"
    Const dwsName As String = "Table"
    Const dfCol As String = "A"
    
    ' References
    
    ' Source
    Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = swb.Worksheets(swsName)
    Dim stbl As ListObject: Set stbl = sws.ListObjects(stblName)
    Dim srg As Range: Set srg = stbl.DataBodyRange.Resize(, sfCols)
    ' Destination
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' Workbooks.Open(dFilePath)
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dwsName)
    Dim dfCell As Range
    Set dfCell = dws.Cells(dws.Rows.Count, dfCol).End(xlUp).Offset(1)
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    ' Do
    
    ' Copy (by assignment)
    drg.Value = srg.Value
    ' Save & Close
    dwb.Close SaveChanges:=True
    ' Inform user.
    MsgBox "Table data backed up.", vbInformation, "Backup Daily"
    
End Sub

Sheet Module e.g. Sheet1 (or wherever the location of the command button

Option Explicit

Private Sub CommandButton3_Click()
    BackupDaily
End Sub

copy a table in 1 workbook to another workbook

1 Answers1

Bakup Table Data