Copy info form one sheet to another based on criteria

Fab88 · Apr 18th 2016

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

Code

Private Sub CommandButton21_Click()


   
    Sheets("S37").Select
    RowCount = Cells(Cells.Rows.Count, "b").End(xlUp).Row
    For I = 1 To RowCount
        Range("b" & I).Select
        check_value = ActiveCell
        If check_value = "True" Or check_value = "true" Then
            ActiveCell.EntireRow.Copy
            Sheets("Scorecard").Select
            RowCount = Cells(Cells.Rows.Count, "c").End(xlUp).Row
            Range("b" & RowCount + 1).Select
            ActiveSheet.Paste
            Sheets("S37").Select
        End If
    Next




End Sub

Display More

I greatly appreciate any help!

Kind regards

**StephenR** · Apr 18th 2016

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

Welcome to Ozgrid.

Try this:

Code

Private Sub CommandButton21_Click()
Dim RowCount as Long, i as Long
With Sheets("S37")
    RowCount = .Cells(.Rows.Count, "b").End(xlUp).Row
    For i = 1 To RowCount
        If LCase(.Range("B" & i).Value) = "true" Then
            .Range("B" & i).EntireRow.Copy Sheets("Scorecard").Range("C" & Rows.Count).End(xlUp)(2)
        End If
    Next
End With
     
End Sub

Display More

Fab88 · Apr 18th 2016

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!

**StephenR** · Apr 18th 2016

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)

Fab88 · Apr 18th 2016

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

**StephenR** · Apr 18th 2016

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

It will paste it in the first blank row of column C on the scorecard sheet, which I think is what you wanted?

Fab88 · Apr 18th 2016

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

**StephenR** · Apr 18th 2016

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.

Code

Private Sub CommandButton21_Click()


Dim RowCount As Long, i As Long, r As Long


With Sheets("S37")
    RowCount = .Cells(.Rows.Count, "b").End(xlUp).Row
    For i = 1 To RowCount
        If LCase(.Range("B" & i).Value) = "true" Then
            r = WorksheetFunction.Max(122, Sheets("Scorecard").Range("C" & Rows.Count).End(xlUp).Row + 1)
            .Range("B" & i).EntireRow.Copy Sheets("Scorecard").Range("C" & r)
        End If
    Next
End With
     
End Sub

Display More

Fab88 · Apr 19th 2016

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?

**StephenR** · Apr 19th 2016

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?

Fab88 · Apr 19th 2016

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!

Copy info form one sheet to another based on criteria

Participate now!

Share