VBA Code to copy/paste rows of data based on cell value 1

[EVE734]

Hello,

I am trying to create a macro in Excel in which for a given range of data, say A32:L100, if a cell in column C has a certain value, the macro will copy and paste data from that row onto another sheet in the next empty row. Below is an example of the code I have, which gives the right general idea, but instead of pasting to a particular cell, I want it to go to the next available row (and of course, it would be nicer if I didn't have to specify row by row which cell to evaluate).

Sheets("JOURNAL ENTRIES").Select
Range("C32").Select
If (Range("C32") = "PBD SURG FOREIGN PAT") Then
Range("A32:L32").Copy
Sheets("PRINT SHEETS").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Sheets("JOURNAL ENTRIES").Select
Range("C33").Select
If (Range("C33") = "PBD SURG FOREIGN PAT") Then
Range("A33:L33").Copy
Sheets("PRINT SHEETS").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Sheets("JOURNAL ENTRIES").Select
Range("C34").Select
If (Range("C34") = "PBD SURG FOREIGN PAT") Then
Range("A34:L34").Copy
Sheets("PRINT SHEETS").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
EndSub

I hope this makes sense and I appreciate any help.

Evelyn

 

[SkipVought]

hi,

    Dim lRow As Long
    
    With Sheets("JOURNAL ENTRIES")
        For lRow = 32 To 34
            If .Cells(lRow, "C").Value = "PBD SURG FOREIGN PAT" Then
                Range(.Cells(lRow, "A"), .Cells(lRow, "L")).Copy
                
                With Sheets("PRINT SHEETS")
                    .Cells(.[A2].End(xlDown).Row + 1, 1).PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=False, _
                        Transpose:=False
                End With
            End If
        Next
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EVE734]

Thanks so much Skip. I am getting an application-defined ir object-defined error (run-time error 1004), with the following section highlighted:
.Cells(.[A2].End(xlDown).Row + 1, 1).PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


Do you know what is wrong?
Thanks,
Evelyn

 

[SkipVought]

1. Please describe exactly what you have in column A in sheet PRINT SHEETS.

2. What do you expect to happen on PRINT SHEETS?

Please answer both.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EVE734]

1) The only thing on the Print Sheets is a "header row" in row A, so that when the data from "Journal Entries" is pasted, it is clear what the data represents.

2)The Journal Entries sheet has multiple rows of data which I need to keep as is, but for some users who will be viewing the file, I want them to be able to see just certain rows grouped together based on the value in column C. My plan would be to add additional macros to group other values from columm C in separate sections of the Print Sheets worksheet. These users could then just view one section for review or printing.

Thanks again,
Evelyn

 

[PHV]

What about this ?
.Cells(.[A[!]1[/!]].End(xlDown).Row + 1, 1).PasteSpecial _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

dim lRow as long


     With Sheets("PRINT SHEETS")
        if Trim(.[A1].offset(1).value) ="" then
           lRow = 2
        else
           lRow = .[A1].End(xlDown).Row + 1
        end if

        .Cells(lRow, 1).PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
     End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops! Sorry eve. I see that lRow is already a variable. Just declare another long data type and assign it in this code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EVE734]

Skip - It worked! You've helped me in the past as well - I really appreciate it your taking the time to help a novice through the fascinating world of VBA!

Sincerely,
Evelyn