Copy info form one sheet to another based on criteria

  • Hi,


    I have multiple sheets from which I would like to copy information from to one general sheet (sheet 1). To let Excel know whether it has to copy the information, I decided to use an extra column in those sheets with the criteria to return a "true" value.
    Now I have read some other threads and this is a macro that I have started trying to adapt to my specific situation but somehow I keep failing.


    more info:
    - b is each time the column in my multiple sheets that returns the true or false value. The information in the row matching the true value should be copied to Sheet1
    - "S37" is the name of one of my sheets from which I would need to copy information from. I would still need to adjust it to multiple sheets.
    - Excel gives me an array error message with this code



    I greatly appreciate any help!


    Kind regards

  • Re: Copy info form one sheet to another based on criteria


    Welcome to Ozgrid.


    Try this:

  • Re: Copy info form one sheet to another based on criteria


    Hi StephenR, thanks for your quick reply!
    I have tried your code and it seems to work, the only problem is that I don't exactly understand where in the code we tell excel where to paste the info in my sheet "Scorecard".
    I suspect it is here?


    Code
    [COLOR=#333333]Sheets("Scorecard").Range("C" & Rows.Count).End(xlUp)(2)[/COLOR]


    Because right now, excel won't paste the info because the copy area and the paste area are not the same size and shape. So I need to rework where it should paste the information.


    Thanks!

  • Re: Copy info form one sheet to another based on criteria


    Yes, that's right (btw colour tags don't work inside code tags as you can see).


    Rather than pasting the entire row I suspect you only want to paste a number of columns? Eg if you only wanted to paste 5 columns you could change that line to

    Code
    .Range("B" & i).resize(,5).Copy Sheets("Scorecard").Range("C" & Rows.Count).End(xlUp)(2)
  • Re: Copy info form one sheet to another based on criteria


    Thanks, that is already an improvement but where exactly will it paste the information in my Scorecard sheet? In the exact same place as it found the information in sheet S37?
    Because I want it the paste the info starting from a certain row or in a certain row

  • Re: Copy info form one sheet to another based on criteria


    No I would like it to paste it in the first blank row starting from a certain row (eg row 122), how would I incorporate this into the code?


    Thanks for your patience btw :)

  • Re: Copy info form one sheet to another based on criteria


    Try this. If you don't want it in column C just change the two references to it.

  • Re: Copy info form one sheet to another based on criteria


    The code as such works but I get the same comment each time: The information cannot be pasted because the copy area and the paste area are not the same size and shape. I have made sure that cells are unmerged and that my sheets are ungrouped. So I don't know what the problem could be?


  • Re: Copy info form one sheet to another based on criteria


    Time to post a workbook with the code you are using - I notice I used entirerow in the code above but I assume you replaced that with something else?

  • Re: Copy info form one sheet to another based on criteria


    Okay Stephen, I will be travelling for the next couple of days but will post a workbook example on Friday. Thanks for your help!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!