Macro for copying rows to another sheet

[jong86]

Hi,

I'm trying to create a macro that looks at my source data, and if the data in a certain column (column B) contains either A,B, or C then it would copy the entire row over to another sheet. If it contained a blank or D,E, F, etc,etc then it would just ignore that and move to the next row.

I have found a macro that can do this, however I need to copy the values only and not the formulas. Below you can find a macro that I have found earlier which works really well. But the formulas are copied and I need the values. It would be great if anyone could help me.

Thanks a lot!



Sub acopy()
SrcLR = Application.Match("*", Sheets("source").Range("B:B"), -1)
DestLR = Application.CountA(Sheets("final").Range("B:B"))
For i = 1 To SrcLR
b = UCase(Sheets("source").Cells(i, "B"))
If b = "A" Or b = "B" Or b = "C" Then
DestLR = DestLR + 1
Sheets("source").Cells(i, 1).EntireRow.Copy Sheets("final").Cells(DestLR, 1)
End If
Next i
End Sub​

 

[JackDanIce]

Welcome to the board. Try:

Sub acopy()

SrcLR = Application.Match("*", sheets("source").Range("B:B"), -1)
DestLR = Application.CountA(sheets("final").Range("B:B"))

Application.ScreenUpdating = False

For i = 1 To SrcLR
    b = UCase(sheets("source").Cells(i, "B"))
    If b = "A" Or b = "B" Or b = "C" Then
        DestLR = DestLR + 1
        sheets("source").Cells(i, 1).EntireRow.Copy
        sheets("final").Cells(DestLR, 1).PasteSpecial Paste:=xlValues
    End If
Next i

Application.ScreenUpdating = True

End Sub

 

[jong86]

It worked perfectly, thanks so much!

 

[jong86]

Hi JackDanIce,

I have been trying to make the macro work in different workbooks as well. However, when i insert a column in front of column B (thus creating a new column B) to be used as the new column the selection should be based on, the macro does not work anymore I really cant find the reason for this, as to me it seems like the macro should look at column B, no matter whether i add a new column. Do you know what is wrong?

Thanks!!

 

[JackDanIce]

SrcLR is being defined as the last row in the CURRENT column B of sheet "Source". If you then insert a new column SrcLR isn't being re-defined so still holds the value of the last row in OLD column B

One way around this might be to use an input box and run this macro, AFTER you've inserted the column:

Sub acopy_1()

Dim Src As Worksheet: Set Src = sheets("source")
Dim Dest As Worksheet: Set Src = sheets("final")
Dim SrcLR As Long, DestLR As Long, b as Long
Dim msg As String, col As String

DestLR = sheets("final").Range("B" & Rows.Count).End(xlUp).Row
msg = "Please enter letter of column for data in source sheet: "
col = InputBox(msg)
SrcLR = sheets("source").Range(col & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

With Src
    For i = 1 To SrcLR
        b = UCase(.Cells(i, col))
        If instr("ABC", b) > 0 Then
            DestLR = DestLR + 1
            .Cells(i, 1).EntireRow.Copy
            Dest.Cells(DestLR, 1).PasteSpecial Paste:=xlValues
        End If
    Next i
End With

Set Src = Nothing: Set Dest = Nothing

Application.ScreenUpdating = True


End Sub

It won't check whether you've entered a valid column letter or not though

 

[jong86]

thanks a lot! I am going to try this.
I am totally new to this, so this might be a very silly question. But does the macro only recognize the value? What if the value in column B is the result of a formula, does it still recognize it? If not, how can I change the macro?

 

[JackDanIce]

col = InputBox(msg)
SrcLR = sheets("source").Range(col & Rows.Count).End(xlUp).Row

In these two lines, col is a string variable and an inputbox is asking the user to provide it's value (msg is the message the user see's when the input box is displayed)

Range(col & Rows.Count) goes to the very last row of col and then goes 'up' until it finds a cell with a value (any kind of value, formulae) in it, i.e. the last non-empty cell. The row number of this cell is assigned to SrcLR, a long data type

 

[jong86]

Thanks so much for your reply!

I am working in another file now, with a similair set up. However, now, I am using colomn F as the base from which it should be determined whether a row should be copied. In colomn F the user can select three options. If 'completed' is selected, the whole row should be copied to the second sheet, and each copied row should be pasted to the next empty row. The data on the source sheet starts at row 11 and should be copied to sheet 2, starting from row 11 as well. How should I adjust the above macro to make this work? Or could you help me with a new macro?

Thanks!!!