Forum Discussion

peysg's avatar
peysg
Copper Contributor
May 24, 2022

VBA Automate copy and paste

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!

 

 

Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 24, 2022

    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)

Resources