Copy Data From One Workbooks to Workbooks Automatically (Not Worksheet) In Excel VBA

You can use Excel VBA or Visual Basic for Applications to transfer data from one Excel workbook to another automatically.
The process involves the following steps:

Create two workbooks – (a)EnterData and (b) Postings in a folder. Of course, you can have them in different folders also, if you like
Now create your macro attached to a command button
The macro whose complete code is given below will automate the process

 

Copy Data From One Workbooks to Workbooks Based On Cell Value

Private Sub CommandButton2_Click()
'SAVE 2859


LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

 For i = 2 To LastRow

 If Cells(i, 13) = "2859" Then
 
 Range(Cells(i, 2), Cells(i, 15)).Select
 Selection.Copy

 Workbooks.Open Filename:="C:\Users\C0545121\Desktop\IMP\2859.xlsx"

 Dim p As Integer, q As Integer

 p = Worksheets.Count

 For q = 1 To p


 If ActiveWorkbook.Worksheets(q).Name = "Filter" Then
 Worksheets("2859").Select
 End If

 Next q
 
 'UserForm5.Show

 erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

 ActiveSheet.Cells(erow, 1).Select
 ActiveSheet.Paste
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 Application.CutCopyMode = False
 'Data_Save.Show
 End If

 Next i



End Sub

 

 

Copy Data From One Workbooks to Workbooks Based On COPY SELECTION DATA

Private Sub CommandButton2_Click()

'SAVE 2859


 Range("B2:E8000").Select
 Selection.Copy

 Workbooks.Open Filename:="C:\Users\C0545121\Desktop\IMP\2859.xlsx"

 Dim p As Integer, q As Integer
 p = Worksheets.Count
 For q = 1 To p


 If ActiveWorkbook.Worksheets(q).Name = "Filter" Then
 Worksheets("2859").Select
 End If

 Next q

 erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

 ActiveSheet.Cells(erow, 1).Select
 ActiveSheet.Paste
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 Application.CutCopyMode = False
 'Data_Save.Show
 End If

 Next i



End Sub

 

 

 

Share This Post

10 comments

Leave a Reply

Your email address will not be published.