+ Reply to Thread
Results 1 to 28 of 28

Copy Data from Several sheets to one based on Date range when date is stored in col A

  1. #1
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Copy Data from Several sheets to one based on Date range when date is stored in col A

    Please assist, I have a VBA Marco I'm trying to write that would copy data from one or several worksheets onto a separate sheet within the same workbook based on date information stored in column A of these sheet(s).

    The workbook contains several worksheets with the first column of these containing the date for each record. I wish to have excel return all rows that contain data that falls within the date range selected and place the information on the "Statement Template" sheet starting in row A21.
    Perhaps a popup box to enter the date parameters with a dropdown selection for each sheet to choose or an "ALL"option to retrieve information from all sheets.
    I have attached a copy of the spreadsheet with three of the sheets containing data and the Statement Template sheet.
    Attached Files Attached Files
    Last edited by NBVC; 08-18-2011 at 05:02 PM. Reason: Marked Solved per OP request

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi J1s

    You've indicated a file is attached but I don't see it. Please attach a workbook that represents your actual data and that demonstrates what your looking for after the procedure is run. Include your existing code.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    If you're having issues attaching the file, try attaching a zip file of your workbook.

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    I've deleted rows in Sheets 1 and 2 to make your file smaller. Look at the attached and let me know how I'm progressing. I've handled those issues that appeared obvious to me.

    Let me know of issues.
    Attached Files Attached Files

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    Well, in playing with this further, I found some issues. I'll await your feedback.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    Fixed the issues I'd identified. I'm certain there's others...find 'em...let me know.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi John,

    The sample looks great! However, I think there are couple of things that need tweeking.
    It seems that I neglected to mention or include the fact that I actually use the account name to name each sheet (although for the file I uploaded I simple labeled them 1,2,3). It appears that your code expects the sheet name and company name to be different and I think this causes a conflict when I incorporate it into the real workbook. I receive a Run-time error '380'

    "Could not set the RowSource property. Invalid property value."
    Regarding the Start and End Dates, I would rather the option to enter the date parameters in two inputbox and allow the Statement spreadsheet to return values that fall within those parameters rather than the scroll selection providing dates from the data on the account sheet which could reflect multiples of the same date depending on the amount of transactions that occured on the day.
    One other item is regarding the Running Totals Column. The formula is calculating a credit as a negative number and a debit as a positive, it should be the reverse.

    Thanks a bunch!

    Jay

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    Regarding this...it can be done
    I would rather the option to enter the date parameters in two inputbox
    However, it'll take discipline on the users part to enter the date in the correct format.

    This can be dealt with such that only unique dates appear in the list boxes.
    providing dates from the data on the account sheet which could reflect multiples of the same date
    Which option do you prefer, the Input Box approach or the unique dates in a list box (I'd personally go with the unique items in the list boxes...but, it's your call).

    Regarding this
    The formula is calculating a credit as a negative number and a debit as a positive, it should be the reverse.
    I'm a retired CPA...been retired for over 10 years...when did they change THAT?

    I'll look at this...it'll take some rewrite.
    I actually use the account name to name each sheet

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    In the attached I've modified the code for the following:
    1. Sheet Names being the Company Name
    2. Credits are "black" Debits are "red"...felt like I was charging a battery
    3. Changed ListBoxes to ComboBoxes that contain unique dates with Dropdown

    We can still do this (against my better judgment) if you so choose...after all, it's your project
    I would rather the option to enter the date parameters in two InputBox
    Let me know of issues.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi John,

    Code looks great! I'm having a minor problem though. I think its just a matter of when I copy over the code to the 'live' workbook.

    It appears that I call up the dialog box but nothing is displayed inside (No Client name, No dates).
    I wonder if this has to do with the positioning of the sheet labeled 'Constants'.

    Let me know your thoughts.

    Thanks,

    Jay

  11. #11
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    The position of Sheet Constants should have nothing to do with it. Look at Sheet Constants and see if its populated...let me know.

    What are your worksheet names?

  12. #12
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Sheet labeled 'Constants' is populated.

    I have one additional sheet that is not in the format of the account sheets that I have included in my workbook. It is a summary of the current balances by line.

    Although nothing is showing in dropdown selection boxes if i select the 'All Dates' box I get an error message directing me to this line in the code.

    Set Rng = Sheets(strSht).Range(("A4"), Sheets(strSht).Range("A4").End(xlDown))

  13. #13
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    Sorry 'bout that...over the phone, I was walking my daughter through changing her toilet flush valve. She was successful and pretty proud of herself (rightfully so).

    Now, to your issue. Check your Named Ranges and see if WSNames is populated...that's the source for the Client Names ComboBox. If WSNames IS populated then we have other issues.

    We need to deal with this but it's relatively simple.
    I have one additional sheet that is not in the format of the account sheets that I have included in my workbook. It is a summary of the current balances by line.
    You'll need to add that Sheet Name to this line of code (if you'd like me to do so, give me the EXACT Sheet Name)
    Please Login or Register  to view this content.
    I hope your spirits are up and that you'll not get discouraged...whatever's happening is likely rather simple...it's simply difficult to pinpoint 'cause I don't see what you see. Now, if you're able, posting a sample copy of what you're ACTUALLY working with would help tremendously. One of the issues I had in reconfiguring your sample file to use Client Names as Worksheet Names was leading and trailing spaces. This MAY be part of our current issue but I think not.

    I gotta tell you, last evening, after dinner and Grandson duty, I made a few minor mods to the code that enhance the user interface...we'll incorporate those when current things work for you.

  14. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    I'd expect this
    Although nothing is showing in dropdown selection boxes if i select the 'All Dates' box I get an error message directing me to this line in the code.

    Set Rng = Sheets(strSht).Range(("A4"), Sheets(strSht).Range("A4").End(xlDown))
    as since no client name was selected in ComboBox1 strSht hasn't been defined here
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi John,

    I have attempted to copy my sheets to your book instead of moving your code to my existing book.

    Also, as you've recommended I've added the additional code to account for the other sheets
    Please Login or Register  to view this content.
    And sht.Name <> "Summary"
    Please Login or Register  to view this content.
    , etc.

    Now, I receive either an error on the following line of code,
    Please Login or Register  to view this content.
    Set Rng = Sheets(strSht).Range(("A4"), Sheets(strSht).Range("A4").End(xlDown))
    Please Login or Register  to view this content.
    or

    while tinkering with it further i now receive a runtime error 1004 after selecting the client name, dates and clicking create. The error states "while renaming a sheet or chart, you entered an invalid name"

  16. #16
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    Last we talked you were apparently not able to get the sample book and code I posted to work for you. Have you been able to resolve this?

    If you'd like me to trouble shoot this with you, I'll need to see what you're seeing. The sample book works as expected for me.

  17. #17
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi John,

    More tinkering and its almost working. I think some of my sheet names were too long when the statement is produced because the code appends the word " Statement" after the name of the original sheet to create the name for the statement sheet. This may have run more than the sheet name limit.

    Still getting VBA error occasionally for this particular line of code.

    Please Login or Register  to view this content.
    .Range("E22").AutoFill Destination:=Range("E22:E" & LR), Type:=xlFillDefault
    Please Login or Register  to view this content.


    James.
    Last edited by J1s; 06-30-2011 at 06:24 PM.

  18. #18
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi James

    If you need help in debugging be glad to help if I can. Post what you have (with revised code) and I'll look at it.

    How did your name change from Jay to James?

  19. #19
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi John,

    I've been vacationing a bit and was unable to access the forum.

    Anyhow, Your code works fine. I see what my problem is in that when there is a client with only one date in which there was a debit or credit and instead of selecting the individual date I click the "all dates" checkbox this generates an error message. But if i just select the individual date available then it works fine.

    If you feel that there is any additional code that should be added to enhance what you've already done please let me know. Otherwise, I will mark this post as solved.

    Oh, by the way my name is in fact James, but my nickname is Jay or J as in J.J., either is fine.

    Thank you again for your diligent efforts in providing the code.

  20. #20
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    This issue is easily fixed
    when there is a client with only one date in which there was a debit or credit and instead of selecting the individual date I click the "all dates" checkbox this generates an error message
    I believe if you change the code to this, that issue should be resolved.
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    I'm not sure how to approach this one. I guess I'd somehow tack that code on to the end of the end of the PROCESSALLDATES macro

    Please Login or Register  to view this content.
    ??

    Also, the new statement sheet is not carrying the format from the template. Currently, I've been simply formating it manually but there must be a better solution. Do you have a quick fix for this also?

    Thanks,

    Jay

  22. #22
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    What code are you referring to when you say
    tack that code on to the end of the end of the PROCESSALLDATES macro
    I'm in left field...fill me in... I have no clue what you're asking.

    Regarding this
    the new statement sheet is not carrying the format from the template
    In the file I 'm working with the statement looks exactly like the template less the "Produce Statement" button. So, what am I missing?

  23. #23
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Previously, you've written the code that I copied on to the previous post. I guess my question is where am I to post the following code:
    Please Login or Register  to view this content.
    .

    Regarding the statement layout. When I attempt to print the new statement the Print layout is in portrait whereas the template is landscape. I've also added a logo to the template which is not being carried over to the statement.

    Thanks,

    Jay

  24. #24
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    Yes, the code snippet will go here
    Please Login or Register  to view this content.
    Regarding this
    When I attempt to print the new statement the Print layout is in portrait whereas the template is landscape. I've also added a logo to the template which is not being carried over to the statement.
    Are you printing the new statement with code or doing it manually? If with code we can change the orientation to landscape. If you're doing it manually you'll either need to change the defaults in the printer setup or change it manually each time.

    The Template I'm working with has no logo...show me what you have and I'll see what I can do.

  25. #25
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hello John,

    Attached is what I'm working with. I was hoping to have the page setup carry over to any of the statements that are generated (landscape, print titles, etc.). This would also include the logo.
    Thanks,

    Jay
    Attached Files Attached Files

  26. #26
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay
    Sorry for the delay...traveling today. Try the code in the attached...see it it does as required. I believe both issues are covered.
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    06-10-2011
    Location
    United States, New York
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi John,

    Your code is working wonders!!

  28. #28
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Copy Data from Several sheets to one based on Date range when date is stored in c

    Hi Jay

    Glad all is working well. If all is well your Thread should be marked as "Solved". You've probably missed the window of opportunity to do this and may have to PM a Moderator to do so.

    "Rule 9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it."

    Interesting project. A pleasure being involved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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