Macro to open a workbook, grab data based on the last date updated in the current workbook

Hello Experts!
I am attempting to write a macro that will open a workbook, grab data based on the last date updated in the current workbook and append to running data in current workbook. The code I have written will successfully open the required workbook and does copy over data but only one row. It is copying it into row 1 over top of my header and it is not appending the data to keep a running record.
WB1 is my current workbook where I want the data to append
WB2 is the workbook that has the data that I want to copy over.

Sub CopyCardData()
Dim WB1 As Workbook
Dim WB2 As Workbook
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

Set WB1 = Workbooks("WebStats_Data_Combine.xlsm")
Set WS1 = WB1.Sheets("Append_Data")
MaxRow1 = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row
MaxDate = Application.WorksheetFunction.Max(WS1.UsedRange)

Set WB2 = Workbooks.Open("**") `workbook location
Set WS2 = Sheets("Card")
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 = MaxRow + 1
End If

Next I

MsgBox "Transfer Done."


Workbooks("FraudCardIntervals.xlsx").Close


End Sub


I really appreciate any help that you can give me! Thank you in advance.

Jbeets

Wow! that is too awesome! Thank you AlphaFrog for your speedy help!

Jbeets

Hi AlphFrog!
I really appreciate your help and the added detail you provide. I made the suggested changes however I am having another issue. Using the changes you provided I have run my file. It now pulls in the data but it's still not getting all of it. The original file has data out to 9/8 however I am not getting anything past 9/6. I then deleted data from 9/5 onward to see if it would pull up to 9/6 again but now it's not pulling anything past 9/4...

I only updated the two spots in my code where I needed to add the 1.

Thank you for your again and please let me know if you need anything else.

Jbeets

Hi AlphaFrog,
Yes, the file will be updated daily with the exception of over the weekend so the most recent day should be only 1-3 days past.

Your explanation makes sense and I will try that and see if it fixes my problem. If not I will attach a sample file.

Thank you again for helping me understand and fix this problem!
Jbeets

Hi again AlphaFrog,
You have been so awesome and every reply has been right on! I was hoping you could help with one more thing... Using the same code you have corrected (and now worked perfectly) I need to tell it to only pull in the data from columns A, B and C instead of the entire row. Can you help me understand how I will update the code to accomplish this?

Again, thank you so much for your time and your help!
Jbeets

Ok, you are the best! Thank you so much for putting up with all my questions and replying so quickly!

Jbeets

Hi AlphaFrog…Me again  I hope that I am not driving you nuts at this point. I really, really appreciate all of your help and knowledge!
My code is working like a dream, I now have another tab though that I want to add into my final results. So what I did is I created another Sub “Sub Jack()” and then Call that sub for each tab in the workbook (WB2). But I am getting a “Subscript out of range” error after the Call sub when going to the next tab
“Set WS2 = Sheets("Append_Retail_Data")”

Sub CombineAllData()
Dim WB1 As Workbook
Dim WB2 As Workbook
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

'Determines the last Date updated and the Last row

Set WB1 = Workbooks("WebStats_Data_Combine.xlsm")
Set WS1 = WB1.Sheets("Data_Combine")
MaxRow1 = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row + 1
MaxDate = Application.WorksheetFunction.Max(WS1.Columns("A"))

'Opens the worksheet with the running data finds the last updated date and appends the newest dates copying columns A-C

Set WB2 = Workbooks.Open("\\Naeast.ad.jpmorganchase.com\amercfs$\CFSCRB\SERVICELINESHARE01\Operations Strategy\Scheduling\Reports\WebStats_Data\WebStats_Data_Pull.xlsm")
Set WS2 = Sheets("Append_Card_Data")
MaxRow2 = WS2.Range("A" & WS2.Rows.Count).End(xlUp).Row + 1

Call Jack

Set WS2 = Sheets("Append_Retail_Data")
MaxRow2 = WS2.Range("A" & WS2.Rows.Count).End(xlUp).Row + 1

Call Jack

MsgBox "Transfer Done."


Workbooks("WebStats_Data_Pull.xlsm").Close


End Sub

Sub Jack()
For I = 2 To MaxRow2
If WS2.Cells(I, "A") > MaxDate Then
WS2.Rows(I).Range("A1:C1").Copy WS1.Cells(MaxRow1, "A")
MaxRow1 = MaxRow1 + 1

End If

Next I
End Sub

Again, thank you so much for all of your help!
Jbeets

Actually- I figured out the Sub out of range error – my tab name was incorrect….so it now runs but it didn’t add any of the data from the second tab “Set WS2 = Sheets("Append_Retail_Data")”.
I don’t know if that is because the data is the same when it comes to the dates that it is looking at or what the problem is 

Ok that makes sense- thank you!!

Jbeets