Auto Copy Data from Excel Workbook Based on Criteria using VBA

[nryan]

Hi All,

I want to automatically copy data from an old Excel Workbook into a new workbook based on criteria using VBA. I already found a good example of this here:

Copy Data to Another Excel WorkBook Based on Criteria Using VBA | Excel VBA Training Videos

However, this example puts the VBA code into the old Excel workbook. I can't modify the old so I need help "flipping" this code to put it in the new workbook and have it work.

Here's the code in the example:

[COLOR=#000000]Sub mySales()

Dim LastRow As Integer, i As Integer, erow As Integer[/COLOR]
[COLOR=#000000]LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=#000000]
For i = 2 To LastRow[/COLOR]
[COLOR=#000000]
If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then
Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy[/COLOR]
[COLOR=#000000]
Workbooks.Open Filename:=”C:\Users\takyar\Documents\salesmaster-new.xlsx”
Worksheets(“Sheet1″).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/COLOR]
[COLOR=#000000]
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If[/COLOR]
[COLOR=#000000]
Next i
[/COLOR]
[COLOR=#000000]End Sub[/COLOR]

Thanks for your help.
-Nick

 

[JoeMo]

Hi All,

I want to automatically copy data from an old Excel Workbook into a new workbook based on criteria using VBA. I already found a good example of this here:

Copy Data to Another Excel WorkBook Based on Criteria Using VBA | Excel VBA Training Videos

However, this example puts the VBA code into the old Excel workbook. I can't modify the old so I need help "flipping" this code to put it in the new workbook and have it work.
Thanks for your help.
-Nick

Do you mean you can't accept code in the old workbook?

 

[nryan]

Hi JoeMo,

I mean that I cannot change/modify the old Excel Workbook because it's always in use by our technicians who record data in the workbook frequently. I would have to ask them to stop using it while I update it and that would stall production. I'm sure the code in the example would work fine if put into the old workbook, but I can't stall production.

 

[JoeMo]

Hi JoeMo,

I mean that I cannot change/modify the old Excel Workbook because it's always in use by our technicians who record data in the workbook frequently. I would have to ask them to stop using it while I update it and that would stall production. I'm sure the code in the example would work fine if put into the old workbook, but I can't stall production.

I'm not following you. You can put the code in any open workbook, including your PERSONAL workbook which is hidden. Just be sure the workbook you want the code to copy from (the source workbook) is the active workbook when you start running the code.

 

[nryan]

I think I know where the confusion is. So in the example, the code would be applied to the workbook from which the data is copied. I want to do the opposite: apply the code to the workbook to which the data is pasted. Does that make sense? I'm not very savvy with VBA yet so I'll probably need help with the code.

Thanks

 

[JoeMo]

I think I know where the confusion is. So in the example, the code would be applied to the workbook from which the data is copied. I want to do the opposite: apply the code to the workbook to which the data is pasted. Does that make sense? I'm not very savvy with VBA yet so I'll probably need help with the code.

Thanks

Put the code in the workbook in which the data is pasted. Then make the workbook from which the data is copied the active workbook. Then press Alt + F8, select All Open Workbooks, find the macro mySales and click Run.

 

[nryan]

This is another good example of what I want to do:

http://www.mrexcel.com/forum/excel-...another-workbook-regularly-automatically.html

This closer to exactly what I want to do. And the guy provided code to follow which is really nice. The only thing with this is I would need to put VBA code into the excel workbook which the data is held (aka the old workbook) which I can't do.

 

[JoeMo]

This is another good example of what I want to do:

http://www.mrexcel.com/forum/excel-...another-workbook-regularly-automatically.html

This closer to exactly what I want to do. And the guy provided code to follow which is really nice. The only thing with this is I would need to put VBA code into the excel workbook which the data is held (aka the old workbook) which I can't do.

I haven't looked at that code, but once again, you can run the code from any open workbook if you configure it correctly.

 

[nryan]

Maybe I can get help with the code. Below I've tried switching around the example code so I can put it in the new workbook (where the data is pasted):

[COLOR=#000000]Sub mySales()[/COLOR][COLOR=#000000]

Dim LastRow As Integer, i As Integer, erow As Integer[/COLOR]
[COLOR=#000000]LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=#000000]
[/COLOR][COLOR=#000000]Workbooks.Open Filename:=”C:\Users\takyar\Documents\salesmaster-new.xlsx”   'Is this line sufficient? I just pulled it out of the If statement from the original example.[/COLOR][COLOR=#000000]

For i = 2 To LastRow[/COLOR]
[COLOR=#000000]
If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then
Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy[/COLOR]
[COLOR=#000000]
ThisWorkbook.ActiveSheet.Worksheets("Sheet1")     'This is my best guess for the code to use to activate "Sheet1" the workbook.
Worksheets(“Sheet1″).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/COLOR]
[COLOR=#000000]
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If[/COLOR]
[COLOR=#000000]
Next i
[/COLOR][COLOR=#000000]
End Sub[/COLOR]

I commented on the left of the lines of code I'm uncertain about.