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!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Why does this error out? 1

Status
Not open for further replies.
Groves22

Groves22

Technical User
Jan 29, 2009
102
US
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
 
Sort by date Sort by votes
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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote



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]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Thanks chandlm! It works!

I don't know why it didn't error out before though....
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
274
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
545
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
561
morechocolate
morechocolate
huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
195
strongm
strongm
sxschech
  • Locked
  • Question
For Each Loop works once then gives error 91 2
Replies
19
Views
338
sxschech
sxschech

Part and Inventory Search

Sponsor

Back
Top