+ Reply to Thread
Results 1 to 10 of 10

Copy filtered data, not header

  1. #1
    Registered User
    Join Date
    11-05-2010
    Location
    Calgary
    MS-Off Ver
    Excel 2007
    Posts
    19

    Copy filtered data, not header

    I am using the following code to copy filtered data ONLY from 6 worksheets and paste the data to a new worksheet. It works fine, except when there is no data. When there is no data, the code copies cell A1 from the header. i do not want the header copied.

    If there is a way to clean up the code, that would be helpful as well.



    Please Login or Register  to view this content.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copy filtered data, not header

    Like so:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-05-2010
    Location
    Calgary
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Copy filtered data, not header

    The following from your code gets highlight as an error,

    Please Login or Register  to view this content.
    I don't fully understand the code, all the worksheets (FLR", "PLC", "KWT", "KRP", "SWS", "KTO) are in one workbook. Sounds like the code is referenceing different workbooks, probably wrong, but thought I'd mention it.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copy filtered data, not header

    That line of code means: "go through this list of worksheets one at a time". So it is referencing sheets in the activeworkbook.

    Are you getting a subscript out of range error? Check the spelling of the sheets I listed in that array. Either it's spelled wrong in the macro or in the sheet tab.

    If you continue to have issues, click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.
    Last edited by JBeaucaire; 12-07-2010 at 04:15 PM.

  5. #5
    Registered User
    Join Date
    11-05-2010
    Location
    Calgary
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Copy filtered data, not header

    OK, I re-wrote the names of the sheets in the ordeer they appear, not sure if that was the problem or not, but it seem to get past it. I now get a run time error 1004 - No cells were found and this bit of code highlighted.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copy filtered data, not header

    1) The order the sheets exist in the workbook is irrelevant, the array is addressing them by name so it can find them wherever they may be. Whatever you did with the sheets fixed the spelling errors is most likely what occurred, or the wrong workbook was "active" when you tested... I do that a lot.

    2) When that error occurs, what is the value of LR? What is the actual LR of visible data on the current ws being parsed?

    Click DEBUG
    Press Ctrl-G to open the Immediate window
    Enter these commands:

    ? ws.Name

    ? LR

    No chance I can look at what you're looking at?

  7. #7
    Registered User
    Join Date
    11-05-2010
    Location
    Calgary
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Copy filtered data, not header

    For " ? ws.Name" I get KWT and for "? LR" I get 847.

    On this ws, there is 847 rows of data, all of which are filtered out at this time.

    UPDATE: I now have data in KWT and the macro works great. For some reason, it does not like KWT with no data, even though SWS, KTO and KRP have no data.
    Last edited by melbri; 12-07-2010 at 05:07 PM.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copy filtered data, not header

    Ok, let's change the method of LR discovery. Is column A a good option? Is row 1 a good option for LC? It's always visible, right?

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    11-05-2010
    Location
    Calgary
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Copy filtered data, not header

    Your first bit of code seems to work fine, but the second works only if there is data. If the filter does not have any data, the code does notlike it.

    I attempted to do a ws similar to what I am working wit, but both sets of code worked on it. Maybe it has to do with that I am pulling the data from sharepoint.

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copy filtered data, not header

    Maybe it has to do with that I am pulling the data from sharepoint.
    Maybe. If you want me to look at a sample workbook in the state it is in right before you run the macro, I can do that. Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

+ 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