Why does this error out? 1

Groves22 · Mar 2, 2009

I have run this code before without problems, but now I am getting an error. Something about the object doesn't like the property.

The bold text is when the error occurs.

Code:

'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & dYear & dMonth & dDay & " Quotes - M.xls")
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
    Sheets(2).Columns("AJ").Copy Sheets(1).Range("B1")
    Sheets(2).ShowAllData
    
Set Quotes_M = Workbooks(dYear & dMonth & dDay & " Quotes - M.xls")

P_Profile.Sheets(4).Range("A4:GL65500").ClearContents

'Filter and paste policy data from data sheet to profile tab
With Quotes_M.Sheets(2)
    .Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    .Range("F2:H2", .Range("F2:H2").End(xlDown)).Copy P_Profile.Sheets(4).Range("B3")
    .Range("I2:M2", .Range("I2:M2").End(xlDown)).Copy P_Profile.Sheets(4).Range("F3")
    .Range("S2:X2", .Range("S2:X2").End(xlDown)).Copy P_Profile.Sheets(4).Range("N3")
    .Range("AA2:AG2", .Range("AA2:AG2").End(xlDown)).Copy P_Profile.Sheets(4).Range("Z3")
    .Range("AH2", .Range("AH2").End(xlDown)).Copy P_Profile.Sheets(4).Range("AH3")
    .Range("AI2", .Range("AI2").End(xlDown)).Copy P_Profile.Sheets(4).Range("AY3")
End With
  
'Copy down formulas in profile tab
With P_Profile.Sheets(4)
    .Activate
    .Range("B1").FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
        Calculate
        count2 = .Range("B1").Value
    .Range("A3").AutoFill Destination:=Range(.Cells(3, 1), .Cells(count2 + 3, 1))
    .Range("E3").AutoFill Destination:=Range(.Cells(3, 5), .Cells(count2 + 3, 5))
End With
 
'Show all data from data tab
Quotes_M.Sheets(2).ShowAllData
P_Profile.Sheets(4).Activate

'Paste Formula's into profile data sheet
RepNum = 4
Call Formula_Paste
        
'Copy sales to quotes tab
With P_Profile
    .Sheets(5).Activate
    [b].Sheets(5).Range(.Cells(3, 1), .Cells(count1 + 3, 52)).Copy[/b]
    .Sheets(4).Select
    .Sheets(4).Cells(count2 + 4, 1).Select
End With

chandlm · Mar 2, 2009

After a quick scan of your code I can't see anywhere where you have declared/assigned a value to count1.

Hope this helps.

Matt
[rockband]

Groves22 · Mar 2, 2009

I left half of the code out for sizing issues the OP. I will show the entire Sub in this post.

Code:

Sub Update_monthly_Profile()

'     *************************************************
'     *             Update monthly Sales              *
'     *************************************************

Application.Calculation = xlCalculationManual
Application.StatusBar = "Updating Monthly Sales."

'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & dYear & dMonth & dDay & " Sales - M.xls")
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
    Sheets(2).Columns("AJ").Copy Sheets(1).Range("B1")
    Sheets(2).ShowAllData
 
'Open and set up monthly sale Profile tab
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Ohio Property Profile v2 " & dMonth & dDay & dYear & ".xls")

Set Sales_M = Workbooks(dYear & dMonth & dDay & " Sales - M.xls")
Set P_Profile = Workbooks("Ohio Property Profile v2 " & dMonth & dDay & dYear & ".xls")
Set P_Macro = Workbooks("OH Property Profile Macros.xls")

Dim i As Integer
For i = 1 To 5
    P_Profile.Sheets(i).Visible = True
Next i

Sheets(5).Range("A4:GL65500").ClearContents

'Filter and paste policy data from data sheet to profile tab
With Sales_M.Sheets(2)
    .Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    .Range("F2:H2", .Range("F2:H2").End(xlDown)).Copy P_Profile.Sheets(5).Range("B3")
    .Range("I2:M2", .Range("I2:M2").End(xlDown)).Copy P_Profile.Sheets(5).Range("F3")
    .Range("S2:X2", .Range("S2:X2").End(xlDown)).Copy P_Profile.Sheets(5).Range("N3")
    .Range("AA2:AG2", .Range("AA2:AG2").End(xlDown)).Copy P_Profile.Sheets(5).Range("Z3")
    .Range("AH2", .Range("AH2").End(xlDown)).Copy P_Profile.Sheets(5).Range("AH3")
    .Range("AI2", .Range("AI2").End(xlDown)).Copy P_Profile.Sheets(5).Range("AY3")
End With
  
'Copy down formulas in profile tab
With P_Profile.Sheets(5)
    .Activate
    .Range("B1").FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
        Calculate
        count1 = .Range("B1").Value
    .Range("A3").AutoFill Destination:=Range(.Cells(3, 1), .Cells(count1 + 3, 1))
    .Range("E3").AutoFill Destination:=Range(.Cells(3, 5), .Cells(count1 + 3, 5))
End With
     
'Show all data from data tab
Sales_M.Sheets(2).ShowAllData
P_Profile.Sheets(5).Activate

'Paste Formula's into profile data sheet
RepNum = 5
Call Formula_Paste
        
'Update formulas for counts
Sheets(5).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(count1 + 3, 194))
Calculate

'Save and close monthly sales data workbook
With Sales_M
    .Application.DisplayAlerts = False
    .Sheets(1).Delete
    .Application.DisplayAlerts = True
    .Save
    .Close
End With

'     **************************************************
'     *             Update monthly Quotes              *
'     **************************************************

Application.StatusBar = "Updating Monthly Quotes."

'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & dYear & dMonth & dDay & " Quotes - M.xls")
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
    Sheets(2).Columns("AJ").Copy Sheets(1).Range("B1")
    Sheets(2).ShowAllData
    
Set Quotes_M = Workbooks(dYear & dMonth & dDay & " Quotes - M.xls")

P_Profile.Sheets(4).Range("A4:GL65500").ClearContents

'Filter and paste policy data from data sheet to profile tab
With Quotes_M.Sheets(2)
    .Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    .Range("F2:H2", .Range("F2:H2").End(xlDown)).Copy P_Profile.Sheets(4).Range("B3")
    .Range("I2:M2", .Range("I2:M2").End(xlDown)).Copy P_Profile.Sheets(4).Range("F3")
    .Range("S2:X2", .Range("S2:X2").End(xlDown)).Copy P_Profile.Sheets(4).Range("N3")
    .Range("AA2:AG2", .Range("AA2:AG2").End(xlDown)).Copy P_Profile.Sheets(4).Range("Z3")
    .Range("AH2", .Range("AH2").End(xlDown)).Copy P_Profile.Sheets(4).Range("AH3")
    .Range("AI2", .Range("AI2").End(xlDown)).Copy P_Profile.Sheets(4).Range("AY3")
End With
  
'Copy down formulas in profile tab
With P_Profile.Sheets(4)
    .Activate
    .Range("B1").FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
        Calculate
        count2 = .Range("B1").Value
    .Range("A3").AutoFill Destination:=Range(.Cells(3, 1), .Cells(count2 + 3, 1))
    .Range("E3").AutoFill Destination:=Range(.Cells(3, 5), .Cells(count2 + 3, 5))
End With
 
'Show all data from data tab
Quotes_M.Sheets(2).ShowAllData
P_Profile.Sheets(4).Activate

'Paste Formula's into profile data sheet
RepNum = 4
Call Formula_Paste
        
'Copy sales to quotes tab
With P_Profile
    .Sheets(5).Activate
    [b].Sheets(5).Range(.Cells(3, 1), .Cells(count1 + 3, 52)).Copy[/b]
    .Sheets(4).Select
    .Sheets(4).Cells(count2 + 4, 1).Select
End With
ActiveSheet.Paste
            
'Update formulas for counts
countT = count1 + count2
P_Profile.Sheets(4).Range("BA3:GL3").AutoFill Destination:=Range(Cells(3, 53), Cells(countT + 4, 194))
Calculate

'Save and close monthly quotes data workbook
With Quotes_M
    .Application.DisplayAlerts = False
    .Sheets(1).Delete
    .Application.DisplayAlerts = True
    .Save
    .Close
End With

End Sub

SkipVought · Mar 2, 2009

Hi,

Be VERY CAREFUL using Sheet Index as a reliable reference. If you have inserted a sheet or change the sheet order in any way, Sheets(5) may not refer to the sheet that you intend.

I personally, use the sheet CodeName, which is not affacted by sheet position or sheet name, both of which users may change.

Skip,
[sup] [glasses]

Don't let the Diatribe...
talk you to death! [tongue]

[/sup][sub]
[glasses]

Just traded in my old subtlety...
for a NUANCE! [tongue]

[/sub]

Groves22 · Mar 2, 2009

Hey Skip...

Thanks for the advice, however, I am the only one that runs and maintains this report.

There will be no sheets added to the P_Profile, it's set in stone! : )

I just don't understand why I get the error now? I have run this macro before, with no problems. Nothing has changed.

chandlm · Mar 2, 2009

Hi Groves22,

I took your code and got the same error using

Code:

.Sheets(5).Range(.Cells(3, 1), .Cells(count1 + 3, 52)).Copy

I then changed it to this and it didn't cause me any issue.

Code:

.Sheets(5).Range(Cells(3, 1), Cells(count1 + 3, 52)).Copy

Hope this helps.

Matt
[rockband]

PHV · Mar 2, 2009

What about this ?
.Sheets(5).Range([!].Sheets(5)[/!].Cells(3, 1), [!].Sheets(5)[/!].Cells(count1 + 3, 52)).Copy

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

Groves22 · Mar 2, 2009

Thanks chandlm! It works!

I don't know why it didn't error out before though....

chandlm · Mar 2, 2009

Glad I could help.

It certainly is weird if you have had no issues before and have definitely not changed anything.

Hope this helps.

Matt
[rockband]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Why does this error out? 1

Groves22

Technical User

chandlm

Programmer

Groves22

Technical User

SkipVought

Programmer

Groves22

Technical User

chandlm

Programmer

PHV

MIS

Groves22

Technical User

chandlm

Programmer

Similar threads

Part and Inventory Search

Sponsor