troubleshooting Question

Copy rows from one workbook to another with criteria

Avatar of swjtx99
swjtx99 asked on
Visual Basic ClassicMicrosoft Excel
6 Comments1 Solution119 ViewsLast Modified:
Hi,

I am trying to adapt some code from an Expert to another problem.

I have two workbooks, Both have dates in column A. I want to open the source pull all rows from the source workbook who's date in column A is greater than the latest date in Column A of the destination workbook (could be any name). The macro runs in the destination workbook. The following runs without errors but doesn't pull any rows over.

Option Explicit


Sub LoadData()
    Dim LastRow As Long
    Dim MainFile As String
    Dim SrcFile As String
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim cCell As Range
    Dim MaxDate As Date
    Dim I As Long, MaxRow1 As Long, MaxRow2 As Long
   
   ' Application.ScreenUpdating = False
    MainFile = ActiveWorkbook.Name 'name of the workbook with the macro which is the destination workbook
    Workbooks.Open "c:\!data/testdata.xls" '<do I need to activate this workbook before proceeding?
    SrcFile = ActiveWorkbook.Name 'name of the source workbook
    Workbooks(SrcFile).Worksheets("Sheet1").Select
   
Dim rng1 As Range ' fixes dates on source workbook
Dim X
Set rng1 = Range([A1], Cells(Rows.Count, "A").End(xlUp))
X = rng1
rng1 = X

   
Set WS1 = Workbooks(SrcFile).Sheets("Sheet1")
MaxRow1 = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row
MaxDate = Application.WorksheetFunction.Max(WS1.Range("A:A"))

Set WS2 = Workbooks(MainFile).Sheets("Raw Data")
MaxRow2 = WS2.Range("A" & WS2.Rows.Count).End(xlUp).Row

For I = 2 To MaxRow2
    If WS2.Cells(I, "A") > MaxDate Then
        WS2.Cells(I, "A").EntireRow.Copy WS1.Cells(MaxRow1, "A")
        MaxRow1 = MaxRow1 + 1
    End If
Next I
   
   
    Application.DisplayAlerts = False  'avoids clipboard message
    Workbooks(SrcFile).Close SaveChanges:=False
    Application.DisplayAlerts = True

    Workbooks(MainFile).Worksheets("Table").Select
     
    Application.ScreenUpdating = True
    MsgBox "Data was successfully imported!", vbInformation
End Sub

Hoping someone can see what I'm doing wrong without having to post example workbooks but I could if necessary.

Thanks in advance,

swjtx99
ASKER CERTIFIED SOLUTION
Michael Fowler
Solutions Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros