+ Reply to Thread
Results 1 to 12 of 12

VBA code to select rows and copy into new sheet

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Question VBA code to select rows and copy into new sheet

    I have a code that will copy all the rows that have a value in row G that is less than today date and paste them into sheet 2. I ran it with dates of 9/14/18 and 10/12/18 - both are less than today but for some reason it is only pulling in the 10/12 date and not the 9/14 date?

    Please Login or Register  to view this content.
    Last edited by Rachel5694; 10-26-2018 at 12:27 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: VBA code to select rows and copy into new sheet

    Hard to know without seeing the spreadsheet, but I have two ideas:

    1) Instead of using Cstr, use CDate

    or

    2) Instead of
    Please Login or Register  to view this content.
    you could try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: VBA code to select rows and copy into new sheet

    It still isn't working :/

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: VBA code to select rows and copy into new sheet

    This works when I test number values but when I change it to "Date()" excel stops working and quits

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: VBA code to select rows and copy into new sheet

    Can you upload the spreadsheet?

  6. #6
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA code to select rows and copy into new sheet

    Hello Rachel,

    As mgs73 suggests, its a good idea to upload a sample of your workbook but in the meantime here is another method that may work for you (untested):-


    Please Login or Register  to view this content.
    I'm assuming that your data starts in row2, Column A with headings in row1. I've also used the sheet codes rather than sheet names in the above code.

    I hope that this helps.

    Cheerio,
    vcoolio.

  7. #7
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: VBA code to select rows and copy into new sheet

    Book1.xlsm

    Here is a sample of my workbook, thanks for helping me on this, I really appreciate it. I don't know why none of the codes will work.

    Vcoolio, that code didn't seem to work :/

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA code to select rows and copy into new sheet

    Hi Rachel5694,

    I did not check you sample book yet, but.

    Have you tired to use Date.Value?

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  9. #9
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: VBA code to select rows and copy into new sheet

    Hi,

    I have not tried Date.Value, where should I enter that?

    Also, I tried this code and it correctly choose the dates. However, it is just cutting them and not pasting them to sheet2. I tried changing the Rows(r).Cut to Rows(r).copy but then it stopped working all together.

    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA code to select rows and copy into new sheet

    Hi Rachel,

    I've noticed two things in your workbook that are the probable causes:-

    1) You've placed the code in the worksheet module. The code is not an event code (i.e. Worksheet_Change) and needs to be placed in a standard module and assigned to a button.

    2) You need to clean up sheet2. Further down the sheet, in various locations, there are a number of blocks of data which would appear to have been placed there by the various codes that you have tried. I'm assuming that you want the data pasted to sheet2 starting in row29 but the transferred data is not starting there as its path is being blocked by the other data further down the sheet. Data is generally pasted from the bottom up, e.g. from my code:

    Please Login or Register  to view this content.
    the pasted data is placed from Column A, the rows are counted from the bottom up [(Rows.Count).End(3)] with End(3) simply being an abbreviation for .End(xlUp) and the (2) being an abbreviation for Offset(1,0) or one row down from the headings or last pasted row.

    I've tested the code in post #6 and it works just fine once the sheet is cleaned up so I would assume that the other codes would work also once the clean up is done.

    I hope that this helps.

    Cheerio,
    vcoolio.

  11. #11
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: VBA code to select rows and copy into new sheet

    That worked! Thank you so much for the help!

  12. #12
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA code to select rows and copy into new sheet

    You're welcome Rachel.
    I'm glad that I was able to help.

    Cheerio,
    vcoolio.

+ 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. VBA code to select all data then copy and paste all unique value to another sheet
    By ahs004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2013, 09:07 AM
  2. select, copy, and paste multiple different rows to new sheet
    By prjames3 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-10-2013, 12:42 PM
  3. VBA Code to Select Multiple Rows and Copy
    By kinseld5 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-20-2012, 12:15 PM
  4. Find, Conditional Select Rows, Copy and Paste to new sheet
    By FN2010 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-26-2010, 12:22 PM
  5. Select cells, then copy those rows to new sheet
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2009, 02:19 PM
  6. Code that will select and copy only so many rows
    By Traymond in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2009, 12:10 PM
  7. copy select rows on 2 sheets to a 3rd sheet
    By thedeacon in forum Excel General
    Replies: 2
    Last Post: 03-26-2009, 02:29 PM
  8. Select rows and copy to another sheet.
    By Juan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-28-2005, 08:06 PM

Tags for this Thread

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