VBA Automate copy and paste

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)