Excel run-time error 1004 after copying data from PDF

Question

I'm doing an extraction from a PDF file and pasting it into Excel, which it works great. When I try to process the extracted data I keep getting a 'Run-Time error 1004'

The code I have is the following

Sub PDF_Upload()
    Dim objWord As Object
    Dim objDoc As Object
    Dim wdFileName
    Dim LastRow As Long
    Dim fso As New FileSystemObject
    Dim fileName As String

    fileName = fso.GetFileName("C:\ABC.pdf")
    Title = Replace(fileName, ".pdf", "")

    Set objWord = CreateObject("word.Application")
    wdFileName = "C:\ABC.pdf"

    Set objDoc = objWord.Documents.Open(wdFileName)
    objWord.Selection.WholeStory
    objWord.Selection.Copy

    ' create new sheet and call it the pdf name
    Sheets.Add
    ActiveSheet.name = Title
    [A1].Select
    ActiveSheet.Paste

    ' Close Word
    objDoc.Close SaveChanges:=False
    objWord.Quit
    Application.DisplayAlerts = True

    ' check
    Sheets(Title).Select
    ' process fails in the next line
    LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
End Sub

Everything works until I get to the last line 'LastRow = ActiveSheet....'

Any ideas on what is causing the error. I have look into other posting on 1004 error in StackOverflow (1,2,3,...), but I can not find anything that helps.

That line is overcomplicated. `LastRow = Sheets("Title").Cells(Rows.Count, 1).End(xlUp).Row`. The `Offset` from `A1` is unnecessary. — BigBen, Sep 13 '18 at 13:12
@BigBen, I've changed it as you suggested, but I'm still getting the same error. — Selrac, Sep 13 '18 at 13:30
@BigBen: Your suggestion fails if the `ActiveSheet` is not a `Worksheet`. Better: `lLastRow = Worksheets("Title").Cells(Worksheets("Title").Rows.Count, 1).End(xlUp).Row`. — Axel Richter, Sep 13 '18 at 13:34
Or use `With... End With` to avoid repeating `Worksheets ("Title")`. — BigBen, Sep 13 '18 at 13:37
@BigBen, thanks. I managed to make it work with With... End with — Selrac, Sep 13 '18 at 14:36
Great! Note that it's @AxelRichter not "@alex" - but glad your problem is resolved. — BigBen, Sep 13 '18 at 14:38
@AxelRichter, sorry misspelled the reference on previous comment. Thanks also for your help — Selrac, Sep 13 '18 at 14:39

score 0 · Answer 1 · answered Sep 13 '18 at 14:41

0

Thanks to the comments above managed to get the solution. Posting it here to help someone:

With Worksheets(Title)
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

answered Sep 13 '18 at 14:41

Selrac

2,203
9
41
84

Just realized that `Title` was a variable, not the actual name of the `Worksheet`. My bad on including the quotation marks around it in the comments. – BigBen Sep 13 '18 at 14:45

Excel run-time error 1004 after copying data from PDF

1 Answers1