Error in VBA I have been running for a year

[jondavis1987]

What's in asterisks will give me an error. Run-time error '91': Object variable or With block variable not set. This code has worked for me for over a year. I haven't changed anything and now i'm getting this error.

ption Explicit


Sub Open_Workbook()


    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
   
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook




'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Stockpile Gradation\Stockpile Charts.xlsx"
    Set destWB = ActiveWorkbook
   
'   Unhide Sheet
    destWB.Sheets("Sheet1").Visible = True
    destWB.Sheets("Moistures").Visible = True
   
'   Find last row of Sheet1 data in destination workbook
    lastRow = destWB.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
   
'   Copy Sheet1 data from source workbook to destination workbook
    srcWB.ActiveSheet.Range("F5").Copy
    destWB.Sheets("Sheet1").Range("A" & lastRow).Resize(14, 1).PasteSpecial xlPasteValues
    srcWB.ActiveSheet.Range("B4").Copy
    destWB.Sheets("Sheet1").Range("D" & lastRow).Resize(14, 1).PasteSpecial xlPasteValues
    srcWB.ActiveSheet.Range("B5").Copy
    destWB.Sheets("Sheet1").Range("E" & lastRow).Resize(14, 1).PasteSpecial xlPasteValues
    srcWB.ActiveSheet.Range("A12:A25").Copy
    destWB.Sheets("Sheet1").Range("F" & lastRow).PasteSpecial xlPasteValues
    srcWB.ActiveSheet.Range("F12:F25").Copy
    destWB.Sheets("Sheet1").Range("G" & lastRow).PasteSpecial xlPasteValues
     
     
      '   Find last row of Sheet1 data in destination workbook
    lastRow = destWB.Sheets("Moistures").Cells(Rows.Count, "A").End(xlUp).Row + 1

'   Copy Moistures data from source workbook to destination workbook
    srcWB.ActiveSheet.Range("F5").Copy
    destWB.Sheets("Moistures").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.ActiveSheet.Range("B4").Copy
    destWB.Sheets("Moistures").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.ActiveSheet.Range("B5").Copy
    destWB.Sheets("Moistures").Range("E" & lastRow).PasteSpecial xlPasteValues
    srcWB.ActiveSheet.Range("F8").Copy
    destWB.Sheets("Moistures").Range("F" & lastRow).PasteSpecial xlPasteValues



'   Hide Sheet
    destWB.Sheets("Sheet1").Visible = False
    destWB.Sheets("Moistures").Visible = False
   
'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True
   
    Dim srcWB1 As Workbook
    Dim destWB1 As Workbook
    Dim fName1 As String
    Dim lastRows As Long
    Dim destName As String
    Dim wsName As String

'   Capture current workbook as source workbook
    Set srcWB1 = ActiveWorkbook


'   Set the name of the destination workbook
    destName = Range("B1").Text

'   Set the name of the destination worksheet
    wsName = "Agg Gradations"

'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Misc\Gradations Changes\" & destName & ".xlsm"
    Set destWB1 = ActiveWorkbook
   
Dim rg As Range
Dim srcName As String
srcName = srcWB.ActiveSheet.Range("B5")
Application.DisplayAlerts = False
For Each rg In destWB1.Sheets(wsName).Range("A1:Z100") ' change the range here
    If rg = srcName Then GoTo Found
Next rg

Found:

    srcWB.ActiveSheet.Range("L12:L25").Copy
**********  rg.Offset(1, 0).PasteSpecial xlPasteValues ***********



'   Save changes and close destination workbook
    destWB1.Close SaveChanges:=True




'   Export source workbook to PDF
    With srcWB
  Dim LocationName As String
     fName = ActiveSheet.Range("A2").Value
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\Jdavis\Dropbox\Quality Control\Aggregates\Stockpile Gradation\" & fName, Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

 

[JoeMo]

I haven't looked closely at your code, but just prior to the errant line you have:
srcWB.ActiveSheet.Range("F5")
is srcWB always the active workbook when that line is executed?

There are many other references to
srcWB.ActiveSheet.Range("......") in your code, even though you have at least one other workbook open during code execution. I recommend that you establish a reference to the "ActiveSheet" at the outset and use it throughout the code. Something like this:
Dim mySht as Worksheet
Set srcWB = ActiveWorkbook
set mySht = srcWB.ActiveSheet
'rest of code
You can then refer to mySht throughout the code w/o having to qualify the workbook its in.

 

[jondavis1987]

Yes, it's always active.

 

[jondavis1987]

I also just tried this.

Dim sName as String
Set sName = Activesheet.name

I then changed every activesheet to sName. Now i get objectrequired and it highlights sName in Set sName = ActiveSheet.Name

 

[JoeMo]

Yes, it's always active.

At the start of your code, you open the destination workbook. At that point, the source workbook is no longer the active workbook, and the active sheet is in the destination workbook.

 

[JoeMo]

I also just tried this.

Dim sName as String
Set sName = Activesheet.name

I then changed every activesheet to sName. Now i get objectrequired and it highlights sName in Set sName = ActiveSheet.Name

There can one one active sheet, and its always in the active workbook. And you don't Set a string variable like sName. It's not an object. I suggested you set the active worksheet in srcWB to be the worksheet object mySht.

 

[jondavis1987]

if the coding of where it's selected as activesheet is the problem then why has it worked for over a year?

 

[jondavis1987]

and i did set the object in srcworkbook from what i can tell

 

[jondavis1987]

I figured it out. My original code works fine. Somebody made a small change that was barely noticeable in one of the workbooks it opens and looks for a match.

 

[jondavis1987]

And the reason the activesheet selection works is because with the path it selects the src workbook first and then the sheet that was active from the start is now active again.