+ Reply to Thread
Results 1 to 16 of 16

How to select rows based on data in a column off multiple sheets?

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    How to select rows based on data in a column off multiple sheets?

    Hi Guys,

    I got the below code off another website and it works great for what it does but I'm looking at expanding it to copy off multiple spreadsheets ranging down a new page.
    Everything I've tried to use hasn't worked for me and I'd appreciate it if somebody could assist me.



    Please Login or Register  to view this content.
    Any help with this would be greatly appreciated.
    Last edited by arlu1201; 08-20-2013 at 01:14 PM. Reason: Use code tags in future.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to select rows based on data in a column off multiple sheets?

    Hi, wdwinterfang,

    please use code-tags when showing your procedure here for better readability and ease of copying/understanding.

    Maybe only change the Sheet to work with from a given one to ActiveSheet and check if the active sheet is not the one to copy to?
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    Holger,

    Thanks for trying still not working the way I want.

    If you refer to the attachment what I'm trying to do is copy everything from the first 5 sheets into another sheet if there is a value in column B.

    But only if there is a value in column B and I need the headings to copy over so that I know what area it's for.

    Thanks again.
    Attached Files Attached Files

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to select rows based on data in a column off multiple sheets?

    Hi, wdwinterfang,

    maybe this is what you are looking for:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    Thanks Holger, works great.

  6. #6
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    Had a slight problem with this. As it's copying a Formula it's altering the Formula for the sheet and needs to not do that. Is there anyway to get it to paste>Special>values?

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to select rows based on data in a column off multiple sheets?

    Hi, wdwinterfang,

    maybe instead of
    Please Login or Register  to view this content.
    which is copying the area use
    Please Login or Register  to view this content.
    which should copy only the values.

    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    Sorry mate didn't work.

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to select rows based on data in a column off multiple sheets?

    Hi, wdwinterfang,

    sorry but I forgot that there was a range to handle (I just noticed the first line to deal with copying).

    Please try
    Please Login or Register  to view this content.
    instead of
    Please Login or Register  to view this content.
    Ciao,
    Holger

  10. #10
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    Thanks mate worked perfectly... your a genius Holger...

  11. #11
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    Just a question that could help with a problem I'm having. How would I set this up to Paste Values instead of pasting the formula I'm using over?

  12. #12
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to select rows based on data in a column off multiple sheets?

    Hi, wdwinterfang,

    doesn´t

    Please Login or Register  to view this content.
    do that: paste values?

    Ciao,
    Holger

  13. #13
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    No mate it doesn't seem to. Some of the sheets are copying over right but others aren't and I can't seem to work out why.

  14. #14
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to select rows based on data in a column off multiple sheets?

    Hi, wdwinterfang,

    the given code just pastes values, no chance for formulas. Maybe you can attach a workbook which features both values as well as formulas to have a look at (or try clearing the contents from To Pick prior to running the macro).

    Ciao,
    Holger

  15. #15
    Registered User
    Join Date
    04-11-2012
    Location
    Bundaberg, Australia
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to select rows based on data in a column off multiple sheets?

    Holger,

    I've attached the file with data on the sheet it seems to be stuffing up on the most. If you run the macro it should show you what I mean if you look at the Avofresh sheet. It copies the top 3 names and the numbers in the row. by the look it leaves the formula which recalculates and pulls in the incorrect data.
    Attached Files Attached Files

  16. #16
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to select rows based on data in a column off multiple sheets?

    Hi, wdwinterfang,

    the code inside that workbook still features the copy command so that formulas could be copied over:
    Please Login or Register  to view this content.
    You would need to replace that line with
    Please Login or Register  to view this content.
    and check again.

    Ciao,
    Holger

+ 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