+ Reply to Thread
Results 1 to 22 of 22

Copy Specific Columns from a Filtered Range

  1. #1
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Copy Specific Columns from a Filtered Range

    Hi,

    I would need to copy specific columns from a filtered range from a worksheet, with the initial column having the value "Add" followed by the columns copied into a new Worksheet.

    How can it be done via VBA code ? Can anyone help me code it ?


    Sarang

  2. #2
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    Hi,

    Just came across this post, it looks like it can be tweaked a bit to suit my requirement (I'm do not want to copy all columns from source worksheet).

    Export Chunks of Data into New Workbooks

    I've enclosed a sample workbook with data & my requirements.

    My Requirements (refer data in the Sample file attached)

    My Source data starts from Row 5 (excluding Header in Row 4). I want to generate two sheets from this data and save them as as two csv files separately.

    To populate Prod Sheet,
    1. Apply filter on column L and exclude unmatched rows (#N/A)
    2. Apply filter on Remarks column (W) and exclude the value Innov
    3. Copy columns P and F into a new sheet and insert a column with value as "Add" (contents of Prod sheet)

    To populate Innov Sheet,
    1. Apply filter on column L and exclude unmatched rows (#N/A)
    2. Apply filter on Remarks column (W) and exclude the value Prod
    3. Copy columns S and F into a new sheet and insert a column with value as "Add" (contents of Innov sheet)

    Let me know if you need any other information.

    Can someone help me tweak the code in the above thread ?
    Attached Files Attached Files
    Last edited by Saarang84; 05-14-2015 at 11:28 AM.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    Saarang,

    Are you putting headers into the Prod and Innov sheets? I can modify the code if you are not... but right now it would be best if you do, if not you will have a blank row at the top of those sheets with this code, but as I said it can be dealt with if required.
    Please Login or Register  to view this content.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Copy Specific Columns from a Filtered Range

    Hi Saraang,

    Here is my code:
    Please Login or Register  to view this content.
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  5. #5
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    Arkadi's code is fine, though it throws a Runtime error 13 Type Mismatch error, as the conditions in the if statement inside the for loop fail as they are generated via formulas from another module in the same workbook. Hence, the values automatically change to #VALUE!

    The same problems occurs even in k64's code.
    How can this be fixed ?
    Last edited by Saarang84; 05-15-2015 at 06:13 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Copy Specific Columns from a Filtered Range

    I'd have to be a sample to be sure. My first idea, if my code is failing on the Autofiltering, then I would create a temporary values-only copy of the sheet first.

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    Saarang, I don't quite follow... what condition on the sheet is causing the error to happen?

  8. #8
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    Hi,

    I've uploaded a sample workbook after removing insensitive data. There are formulas filled in the range (between columns L to W) which get auto-calculated when macro code gets executed on button click event.

    I'm trying to link the code I'm seeking to build to the last button named Extract Dumps.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    Quote Originally Posted by Arkadi View Post
    Saarang, I don't quite follow... what condition on the sheet is causing the error to happen?
    Hi Arkadi,

    I've included comments in my workbook attached to my below post against your code. Refer to your module which I've renamed as "Export_Dumps_1".

    I tried executing your module by setting breakpoints at specific parts of the code after integrating it with my existing code, but I get VBA run-time error '13' Type Mismatch error due to formulas getting auto-calculated and resulting in #VALUE! filled in the cell being compared against the values "#N/A" and "Prod Only" or "Innov Only".

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    I ran the Extract Dumps and did get the error you menttioned.... but not in my code, it happens well after you call my macro.

    The error I see is on line: .Name = "Dump_Prod_" & Mid(Date, 3, 2) - 1 & MonthName(Left(Date, 1)) & Right(Year(Date), 2)

  11. #11
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    Quote Originally Posted by Arkadi View Post
    I ran the Extract Dumps and did get the error you menttioned.... but not in my code, it happens well after you call my macro.

    The error I see is on line: .Name = "Dump_Prod_" & Mid(Date, 3, 2) - 1 & MonthName(Left(Date, 1)) & Right(Year(Date), 2)
    Kindly populate some random value in the range A5:A28 and re-run the module once again, to get to know the error I'm referring which is sourced from the formula-filled data in the range L to W.

  12. #12
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    I tried running my code manually but of course that caused issues because the sheets didn't exist yet... then I made the sheets and ran it, but my macro calculates last row based on column A and your Raw_Data sheet has no values in Column A... So I changed it to column B, and it works... so I still can't see what is wrong with my macro?

  13. #13
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    Quote Originally Posted by Arkadi View Post
    I tried running my code manually but of course that caused issues because the sheets didn't exist yet... then I made the sheets and ran it, but my macro calculates last row based on column A and your Raw_Data sheet has no values in Column A... So I changed it to column B, and it works... so I still can't see what is wrong with my macro?
    Kindly comment the below lines in the Sub cmdExtDumps_Click() and then try executing the macro.

    .Name = "Dump_Prod_" & Mid(Date, 3, 2) - 1 & MonthName(Left(Date, 1)) & Right(Year(Date), 2)
    .Name = "Dump_Innov_" & Mid(Date, 3, 2) - 1 & MonthName(Left(Date, 1)) & Right(Year(Date), 2)
    .Name = "Dump_Prod_" & Mid(Date, 3, 2) - 1 & MonthName(Left(Date, 1)) & Right(Year(Date), 2) & "_Post_WO"
    .Name = "Dump_Innov_" & Mid(Date, 3, 2) - 1 & MonthName(Left(Date, 1)) & Right(Year(Date), 2) & "Post_WO"


    The error I'm referring to occurs while executing the first if condition inside the For Loop in your module Export_Dumps_1

  14. #14
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    Ok, I tried to put values in column A, then hit the button and Prod and Innov look just fine.... I get no error, and I don't see #Value in either raw data, innov, or prod.... so I still don't understand.

  15. #15
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    Ooooh it happens AFTER some of your other code... my macro is not causing this error.

  16. #16
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    I even commented out where you call my macro, and you still get "#value" on raw_data... so my macro does not cause that at all.

  17. #17
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Copy Specific Columns from a Filtered Range

    Try this:
    Please Login or Register  to view this content.

  18. #18
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    BIG thanks to Arkadi and K64 for their codes !! My requirement is resolved !!

  19. #19
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Copy Specific Columns from a Filtered Range

    You're weclome Saarang84, glad I could help! Thanks for the +rep

  20. #20
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Copy Specific Columns from a Filtered Range

    Out of curiosity Saarang, did k64's code fix it? or did you change something else? I'm very glad it worked out for you in the end

  21. #21
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy Specific Columns from a Filtered Range

    Hi Arkadi / K64,

    Both codes work fine, Arkadi's code check all rows one by one, uses If conditions & copies them to appropriate sheets, whereas K64's code looks more simple & short and gets the work done through AutoFilter.

    @K64 :

    I've never used the Intersect function before, but wonder how it works. Can you brief me about it ?

  22. #22
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671
    Quote Originally Posted by Saarang84 View Post
    Hi Arkadi / K64,

    Both codes work fine, Arkadi's code check all rows one by one, uses If conditions & copies them to appropriate sheets, whereas K64's code looks more simple & short and gets the work done through AutoFilter.

    @K64 :

    I've never used the Intersect function before, but wonder how it works. Can you brief me about it ?
    Intersect returns a range composed of only those cells in common between the intersected ranges. So intersect(range("A:A"), range("1:1")) = range("A1")

+ 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. Copy a specific row and a range of columns to next available row on another worksheet
    By monkey1184 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2013, 05:02 PM
  2. How to copy specific rows of filtered data using VB
    By Menaka in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-01-2013, 01:46 PM
  3. Replies: 8
    Last Post: 04-04-2013, 08:02 PM
  4. copy specific range of rows and columns according to a cell value
    By archangel9999 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2013, 03:41 PM
  5. How to copy a range of columns from sheet1 to sheet2 if the cell show a specific text
    By chermaine123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2011, 02:39 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