+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

    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

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,401

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

    Missing a 1

    MaxRow1 = MaxRow1 + 1
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

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

    Jbeets

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,401

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

    You're welcome.

    Heads up: You probably want to add a 1 to MaxRow1 before you paste the first time. As you have it now, the first time MaxRow1 is defined, it's the last used row and not the next empty row.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

    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

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,401

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

    I couldn't say what the problem was without seeing your data. One guess is this line...

    MaxDate = Application.WorksheetFunction.Max(WS1.UsedRange)

    It finds the largest numeric value (dates included) anywhere on the sheet. The largest numeric value may not be the max date you expect. It could be a number or date from another column. Depending on your data layout, you may want it to specify the max date from just one column on the sheet e.g.;

    MaxDate = Application.WorksheetFunction.Max(WS1.Columns("A"))


    Do you know the actual value of maxdate when you run the code?

    Debugging VBA Code
    Last edited by AlphaFrog; 09-09-2016 at 06:25 PM.

  7. #7
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

    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

  8. #8
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

    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

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,401

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

    WS2.Rows(I).Range("A1:C1").Copy WS1.Cells(MaxRow1, "A")

  10. #10
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

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

    Jbeets

  11. #11
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

    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

  12. #12
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

    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 

  13. #13
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,401

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

    This is a new topic that deserves its own new thread.

  14. #14
    Registered User
    Join Date
    07-17-2014
    Location
    Springfield, MO
    MS-Off Ver
    2007
    Posts
    42

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

    Ok that makes sense- thank you!!

    Jbeets

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] need to open a montly workbook based on a date in current workbook
    By AWITCHER in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-27-2015, 03:02 PM
  2. set datepicker value to current date on workbook open
    By whitwoo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-17-2014, 01:52 PM
  3. Excel Log: VBA to Add Row(s) Upon Workbook Open Last through Current Date
    By jdppep in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2013, 04:22 PM
  4. Replies: 0
    Last Post: 05-08-2013, 02:47 PM
  5. [SOLVED] Copying data from external workbook into current workbook based on cell value
    By Brontosaurus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-07-2012, 11:17 AM
  6. Macro for current or open workbook
    By mma3824 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2012, 03:16 PM
  7. How to open,save and close another workbook from the current workbook using macro?
    By ravikumar00008 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-15-2012, 10:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1