+ Reply to Thread
Results 1 to 10 of 10

Macro to Copy Date within specified range for "overage"

  1. 05-08-2024, 10:43 PM #1
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,776

    Macro to Copy Date within specified range for "overage"

    I have a source file in C:\My documents called Source WIP

    I have tried to write code to open a file and copy the data within a specified date range from row 50 onwards for e.g. 01/04/2024 to say 31/05/2024 Col E (format dd/mm/yyyy") for data containing "Overage" in Col H

    However when running the code no data is being pasted on sheet "Overage Goods"

    It would be appreciated if someone would kindly amend my code

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-09-2024, 02:01 AM #2
    Sintek
    Sintek is offline
    Forum Expert Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,448

    Re: Macro to Copy Date within specified range for "overage"

    Howard...Why not just make use of filter...You have so many previous posts where this is provided...
    Is this how your Source WIP file is setup...All the blank rows and columns from Rows 1 - 49?

    This will give you an idea...Assumes headers in row 1 and data up to row 49 inplace of blanks...
    Please Login or Register  to view this content.
    Last edited by Sintek; 05-09-2024 at 02:09 AM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  3. 05-09-2024, 02:08 AM #3
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,673

    Re: Macro to Copy Date within specified range for "overage"

    Hi,

    In sample files you provided, there are some problems. Namely:
    - no Overaged Settlements sheet in a dest file,
    - source xlsx (not xlsm) type
    - both dates 30/4/2024 (E55:E56) are texts. change call alignment from centered to automatic and you will see these two are left aligned (so it's text), while all other are right aligned (so it's date or number). Make sure in real data you don't face this issue


    Nevertheless, the main problem is 
    Please Login or Register  to view this content.
    while you have in a cell capital O: Overage SH/MLK

    Make your comparison of thext case insensitive by using:
    Please Login or Register  to view this content.
    this shall do the job

    Another idea (but this would be a large change to code): Use filtering and then copying of all visible results in one step. In case of many data rows matching the conditions this approach shall be much quicker than copying data row by row.

    Edit: I can see that this idea was also proposed by sintek while I was writing this answer
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  4. 05-09-2024, 02:56 AM #4
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,776

    Re: Macro to Copy Date within specified range for "overage"

    Thanks for your advise Sintek.

    Row1 will have headers and data in Row 2 to 49


    I amended the code using autofilter nut get a run time error


    Please Login or Register  to view this content.
    Also want to limit selection when browsing to "*WIP*" in c:\my Documents


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 05-09-2024, 02:58 AM #5
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,776

    Re: Macro to Copy Date within specified range for "overage"

    Thanks for your input Kaper

    I used this code which works, but Sintek's suggestion of using autofiltering is far more efficient

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 05-09-2024, 06:01 AM #6
    Sintek
    Sintek is offline
    Forum Expert Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,448

    Re: Macro to Copy Date within specified range for "overage" 

    Please Login or Register  to view this content.
    Last edited by Sintek; 05-09-2024 at 06:05 AM.
    Reply With QuoteRegister To Reply
  7. 05-09-2024, 06:29 AM #7
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,776

    Re: Macro to Copy Date within specified range for "overage"

    Thanks Sintek. I get a run time error with this part of the code

    Please Login or Register  to view this content.
    Kindly test and amend
    Reply With QuoteRegister To Reply
  8. 05-09-2024, 06:50 AM #8
    Sintek
    Sintek is offline
    Forum Expert Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,448

    Re: Macro to Copy Date within specified range for "overage"

    Works for me with your sample files provided data is in rows 1 to 49...and all columns...Not sure what is different in actual files
    Reply With QuoteRegister To Reply
  9. 05-09-2024, 08:12 AM #9
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,776

    Re: Macro to Copy Date within specified range for "overage"

    I closed Excel rerun and it now works
    Reply With QuoteRegister To Reply
  10. 05-09-2024, 08:29 AM #10
    Sintek
    Sintek is offline
    Forum Expert Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,448

    Re: Macro to Copy Date within specified range for "overage"

    Glad you got it sorted...Happy Coding...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto Change Date format from "." to "/" when copy data from SAP T-code - VF05
    By abhinavbinkar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-29-2019, 05:20 AM
  2. If Sheet("Entry").range("P3") has not today date then run macro
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2016, 12:13 PM
  3. Filter by "Confirmed" and by "Date (confirm or order)" copy and paste to "Invoice Audit"
    By celeazul in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-22-2016, 09:21 AM
  4. [SOLVED] Need a macro to copy a range date if a field in a column contains "complete"
    By brigitteMogg in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-12-2012, 11:37 AM
  5. Macro to copy named range "box" values to named range "box2". Both are size of 5x26
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2011, 10:56 AM
  6. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM
  7. How to display a "text value" after matching a "date" in a certain "date range"
    By deverom in forum Excel General
    Replies: 1
    Last Post: 09-15-2010, 01:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1