Getting a Object Required error message with my vba

Question

Getting an error with my vba

I'm trying to have the macro loop through a list, and when a row has something other than 0 in column 12, I want it to copy the information in the first column of that row, and paste it onto a different worksheet in the same workbook.

I'm getting the error message on the 'Cells(1, x).Value.Copy' part.

Sub filter()
    letter = Worksheets("Variables").Range("B23").Value
    x = 2
    Worksheets("ER Data").Select

    Do While Cells(1, x).Value <> ""

        If Cells(12, x).Value <> 0 Then
            Cells(1, x).Value.Copy
            Sheets("Letter").Select
            Range("B10").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,_
                SkipBlanks:=False, Transpose:=False

            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,_
                Filename:=Trim(letter), Quality:=xlQualityStandard,_
                IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=False

            x = x + 1
        Else
            x = x + 1
        End If
    Loop
End Sub

I'd like it to copy and paste as desired.

You have your row and column reversed when calling Cells. If you use Cells(12, 1) then that's cell A12 and not cell L1. Also, you never switch back to the "ER Data" sheet after copying an entry to the "Letter" sheet — barrowc
– barrowc, Commented Jul 22, 2019 at 23:39
@barrowc appreciate the catch, that was obviously a big problem I had. — Lukec
– Lukec, Commented Jul 23, 2019 at 21:03

Harassed Dad · Accepted Answer · 2019-07-23 08:35:59Z

1

Sub filter()
dim letter as long
dim x as long
letter = Worksheets("Variables").Range("B23").Value
x = 2

With Worksheets("ER Data")
    Do While .Cells(1, x).Value <> ""
        If .Cells(12, x).Value <> 0 Then
           Sheets("Letter").Range("B10") = .cells(12,x).value

                 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Trim(letter), Quality:= xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

        End If
        x = x + 1

    Loop
End With
End Sub

answered Jul 23, 2019 at 8:35

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukec Over a year ago

thanks for the syntax, I got a mismatch error though when I ran it. I'll show the syntax that ended up working out for me.

Lukec · Accepted Answer · 2019-07-23 21:29:10Z

0

Sub filter()

letter = Worksheets("Variables").Range("B23").Value
x = 2

Worksheets("ER Data").Select
Do While Cells(x, 1).Value <> ""
Worksheets("ER Data").Select
    If Cells(x, 12).Value <> 0 Then
        Cells(x, 1).Copy
        Sheets("Letter").Select
        Range("B10").Select
        ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Trim(letter), 
Quality:= _
   xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

        x = x + 1
    Else
        x = x + 1
    End If
    Worksheets("ER Data").Select
Loop
End Sub

edited Jul 23, 2019 at 21:29

answered Jul 23, 2019 at 21:04

Lukec

731 silver badge13 bronze badges

Collectives™ on Stack Overflow

Getting a Object Required error message with my vba

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related