VBA Automate copy and paste

[peysg]

Hi All,

I would like to ask for help to more efficiently automate the transfer of data from one excel workbook to another. Is there a way to copy data from individual tab in workbook A to the corresponding tab in workbook B (append the data) without having to write separate macro for each tab? So far i am only seeing code examples where data from a single tab is transfer to another tab in a separate workbook but no example on how I can apply this to multiple tabs at one go..

Thank you!

[#2]

@peysg 

With Macro recorder a simple VBA example... maybe it will help you with your project.

...Addendum...I just saw that it should be transferred to several workbooks.

Here, however, all workbooks should be open. With closed workbooks, the effort is correspondingly "unclosed" large.

this code is with a close workbook

Public Sub Write()
Dim sPath As String ' the folder path of the Excel sheets
Dim sFile As String ' the file to write to
Dim WkSh_Q As Worksheet ' the source worksheet - the origin
Dim WkSh_Z As Worksheet ' the target worksheet - the result
sPath = "D:\Application Data\Excel Data\Excel Files\"
sFile = "Dialogdata.xlsm"
Application.ScreenUpdating = False
If Dir(sPath & sFile) <> "" Then
Workbooks.Open (sPath & sFile)
ThisWorkbook.Activate
'Application.ActiveWindow.Visible = False
else
MsgBox "The specified folder """ & sPath & """" & Chr(10) & _
"and/or the file you are looking for """ & sFile & """ does not exist!", _
16, " Note for " & Application.UserName
Exit Sub
End If
Set WkSh_Q = ThisWorkbook.Worksheets("Default Data")
Set WkSh_Z = Workbooks(sFile).Worksheets("Default Data")
WkSh_Q.Cells.Range("B1:B27").Copy Destination:=WkSh_Z.Range("B1:B27")
Workbooks(sFile).Close SaveChanges:=True
Application.ScreenUpdating = True
MsgBox "The data was submitted successfully.", _
64, " Information for " & Application.UserName
end sub

'Examble from internet, untested.

VBA Code Source

NikolinoDE

I know I don't know anything (Socrates)