What a VBA code to copy table between 2 different workbooks

[Ramadan2512]

I'm stuck with task to copy table from a closed workbook to another workbook
the source workbook name is ("Carnell") and this is the file path "D:\Desktop\carnell.xlsm" with only one sheet named ("Data") it has a table "table1" with updating new data every day
the workbook that i need to pate the table in it is "AUTHS" with different worksheets and i need to paste to a sheet named "info"

I need a way please to update between sheets whenever new data added to the source table eithr automatically with the code or even with a button to update data between them I don't mind to copy only values or with formulas

I have found a simple code on the niternet but it seems that not matching with my files as i get error message say (object doesn't support this property or method

any suggessions please

[
Sub GetDatacClosedBook()

Dim src As Workbook

Setsrc = Workbooks.Open("D:\Desktop\carnell.xlsm", True, True)
ThisWorkbook.Activate

Worksheets("Data").Range("A1:K200").Formula = src.Worksheets("info").Range("A1:k200").Formula

End Sub]

 

[Ramadan2512]

Guys
I found this code while I'm searching for a solution and it's working with me perfectly with just one simple issue I don't know how to fix it

at the end of the vba run it closes my open workbook that I'm pasting to it and open the source file which I got the data from .... I want to be Vice versa
to colse the source file and keep my destination file open

please just fix that rule

[
Sub mycode()

Workbooks.Open Filename:="D:\Desktop\Stop Work.xlsm"
Worksheets("Sheet1").Cells.Select
Selection.Copy

Workbooks.Open Filename:="D:\Desktop\AUTHs.xlsm"
Worksheets("Stop Work").Cells.Select
Selection.PasteSpecial xlPasteAll              'xlPasteAll to paste everything
ActiveWorkbook.Save

ActiveWorkbook.Close SaveChanges:=False         'to close the file
Workbooks("source_file").Close SaveChanges:=True  'to close the file

End Sub]

 

[Skyybot]

This assumes that neither of the Workbooks you are copying and pasting are the current open Workbook.

Option Explicit

Sub DataFromAotherWorkbook()
Dim  srcWB As Workbook, c2WB As Workbook, srcFN As String, c2FN As String, ans As VbMsgBoxResult

Do While srcFN = "False" Or srcFN = ""
    srcFN = Application.GetSaveAsFilename(InitialFileName:="", Title:="Choose a Source Workbook")
    If Not srcFN = "False" And Len(srcFN) > 4 Then Exit Do
    ans = MsgBox("There is no file chosen. Would you like to stop looking?", vbYesNo + vbQuestion, "No File Found")
    If ans = vbYes Then Exit Sub
Loop
Do While c2FN = "False" Or c2FN = ""
    c2FN = Application.GetSaveAsFilename(InitialFileName:="", Title:="Choose a Destination Workbook")
    If Not c2FN = "False" And Len(c2FN) > 4 Then Exit Do
    ans = MsgBox("There is no file chosen. Would you like to stop looking?", vbYesNo + vbQuestion, "No File Found")
    If ans = vbYes Then Exit Sub
Loop

CopySheets srcFN, c2FN

End Sub
Private Sub CopySheets(ByVal wb1 As String, ByVal wb2 As String)
Dim xlApp As Excel.Application, srcWB As Workbook, c2WB As Workbook
Set xlApp = New Excel.Application
Set srcWB = xlApp.Workbooks.Open(wb1)
Set c2WB = xlApp.Workbooks.Open(wb2)
srcWB.Sheets("Sheet1").Cells.Copy c2WB.Sheets("Stop Work").Cells(1, 1)
xlApp.CutCopyMode = False
xlApp.DisplayAlerts = False
c2WB.Save
srcWB.Close
c2WB.Close
xlApp.Quit
End Sub

 

[Ramadan2512]

This assumes that neither of the Workbooks you are copying and pasting are the current open Workbook.

Option Explicit

Sub DataFromAotherWorkbook()
Dim  srcWB As Workbook, c2WB As Workbook, srcFN As String, c2FN As String, ans As VbMsgBoxResult

Do While srcFN = "False" Or srcFN = ""
    srcFN = Application.GetSaveAsFilename(InitialFileName:="", Title:="Choose a Source Workbook")
    If Not srcFN = "False" And Len(srcFN) > 4 Then Exit Do
    ans = MsgBox("There is no file chosen. Would you like to stop looking?", vbYesNo + vbQuestion, "No File Found")
    If ans = vbYes Then Exit Sub
Loop
Do While c2FN = "False" Or c2FN = ""
    c2FN = Application.GetSaveAsFilename(InitialFileName:="", Title:="Choose a Destination Workbook")
    If Not c2FN = "False" And Len(c2FN) > 4 Then Exit Do
    ans = MsgBox("There is no file chosen. Would you like to stop looking?", vbYesNo + vbQuestion, "No File Found")
    If ans = vbYes Then Exit Sub
Loop

CopySheets srcFN, c2FN

End Sub
Private Sub CopySheets(ByVal wb1 As String, ByVal wb2 As String)
Dim xlApp As Excel.Application, srcWB As Workbook, c2WB As Workbook
Set xlApp = New Excel.Application
Set srcWB = xlApp.Workbooks.Open(wb1)
Set c2WB = xlApp.Workbooks.Open(wb2)
srcWB.Sheets("Sheet1").Cells.Copy c2WB.Sheets("Stop Work").Cells(1, 1)
xlApp.CutCopyMode = False
xlApp.DisplayAlerts = False
c2WB.Save
srcWB.Close
c2WB.Close
xlApp.Quit
End Sub

thanks alot for your help but it doesnt copy the data and it even ask me to save my workbook and a new file while it's alreay exist and saved - I just need to copy the data in "sheet1" from "stop work" file to sheet "stop work" in "AUTHS" file and my shared very simple code is working good i just neeed your help to change closing and opening files after run as i explained

 

[Skyybot]

Sub mycode()

Workbooks.Open Filename:="D:\Desktop\Stop Work.xlsm"
Worksheets("Sheet1").Cells.Select
Selection.Copy

Workbooks.Open Filename:="D:\Desktop\AUTHs.xlsm"
Worksheets("Stop Work").Cells.Select
Selection.PasteSpecial xlPasteAll              'xlPasteAll to paste everything

Application.DisplayAlerts = False

WorkBooks("D:\Desktop\Stop Work.xlsm").Close SaveChanges:=False         'to close the file
Workbooks("D:\Desktop\AUTHs.xlsm").Close SaveChanges:=True  'to close the file

Application.DisplayAlerts = True

End Sub

 

[Ramadan2512]

Sub mycode()

Workbooks.Open Filename:="D:\Desktop\Stop Work.xlsm"
Worksheets("Sheet1").Cells.Select
Selection.Copy

Workbooks.Open Filename:="D:\Desktop\AUTHs.xlsm"
Worksheets("Stop Work").Cells.Select
Selection.PasteSpecial xlPasteAll              'xlPasteAll to paste everything

Application.DisplayAlerts = False

WorkBooks("D:\Desktop\Stop Work.xlsm").Close SaveChanges:=False         'to close the file
Workbooks("D:\Desktop\AUTHs.xlsm").Close SaveChanges:=True  'to close the file

Application.DisplayAlerts = True

End Sub

thanks but it gives me an error (subscript out of range) on this line (Workbooks("D:\Desktop\Stop Work.xlsm").Close SaveChanges:=False 'to close the file)

 

[Skyybot]

Apologies. I can't help you there. I don't know where the files are located on your computer. Replace this -D:\Desktop\Stop Work.xlsm - with the filename of the file you want to copy from and replace this - D:\Desktop\AUTHs.xlsm - with the filename of the file you want to copy to.