Error 1004 in one workook, not in another...

C

crichton

New Member
Joined
Sep 25, 2017
Messages
2
Hi have the below code that is generating a 1004 error in one workbook but not another, can anyone see what is wrong with this?? :)

Range("E5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],Roster!C[-4]:C[-3],2,FALSE),""Manager, terminated or not yet in roster"")"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],Roster!C[-5]:C[3],9,FALSE),""Manager, terminated or not in roster"")"

Range("G5").Select
ActiveCell.FormulaR1C1 = "=SUMIFS('Stow Total'!C20,'Stow Total'!C2,[@ID])"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=SUMIFS('Stow Total'!C12,'Stow Total'!C2,[@ID])"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=SUMIFS('Stow Total'!C14,'Stow Total'!C2,[@ID])"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=SUMIFS('Stow Total'!C16,'Stow Total'!C2,[@ID])"


Range("K5").Select
ActiveCell.FormulaR1C1 = _
"=(SUMIFS('S PPR'!C9,'S PPR'!C3,[@ID])+[@[Sum of hrs from LC data]])+(SUMIFS('T PPR'!C9,'T PPR'!C3,[@ID]))"
Range("L5").Select
ActiveCell.FormulaR1C1 = _
"=IF([@[LC Hours]]<40,""LC1 or Manager"",IF([@[LC Hours]]<80,""LC2"",IF([@[LC Hours]]<120,""LC3"",IF([@[LC Hours]]<160,""LC4"",IF([@[LC Hours]]<200,""LC5"",IF([@[LC Hours]]<2000000,""Veteran"",""LC1 or Manager""))))))"
Range("M5").Select
ActiveCell.FormulaR1C1 = _
"=IF([@[LC Hours]]<40,Range!R15C8,IF([@[LC Hours]]<80,Range!R16C8,IF([@[LC Hours]]<120,Range!R17C8,IF([@[LC Hours]]<160,Range!R18C8,IF([@[LC Hours]]<200,Range!R19C8,IF([@[LC Hours]]<240000,Range!R20C8,Range!R15C8))))))"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=SUM(Q1_[@[Small hours]:[Large hours]])"
Sheets("Stow LC summary").Select
Range("L5").Select
ActiveCell.FormulaR1C1 = _
"=IF([@[LC Hours]]<40,""LC1 or Manager"",IF([@[LC Hours]]<80,""LC2"",IF([@[LC Hours]]<120,""LC3"",IF([@[LC Hours]]<160,""LC4"",IF([@[LC Hours]]<200,""LC5"",IF([@[LC Hours]]<2000000,""Veteran"",""LC1 or Manager""))))))"
Range("M5").Select
ActiveCell.FormulaR1C1 = _
"=IF([@[LC Hours]]<40,Range!R15C8,IF([@[LC Hours]]<80,Range!R16C8,IF([@[LC Hours]]<120,Range!R17C8,IF([@[LC Hours]]<160,Range!R18C8,IF([@[LC Hours]]<200,Range!R19C8,IF([@[LC Hours]]<240000,Range!R20C8,Range!R15C8))))))"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=SUM(Q1_[@[Small hours]:[Large hours]])"
Range("O5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Stow Total'!C[-10],'Stow Total'!C2,[@ID]),""0"")"
Range("P5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Stow Total'!C[-10],'Stow Total'!C2,[@ID]),""0"")"
Range("Q5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Stow Total'!C[-10],'Stow Total'!C2,[@ID]),""0"")"
Range("R5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[ET Units Total]]/[@[ET Hours on task]]),""0"")"
Range("S5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@Small]/[@[Small hours]]),""0"")"
Range("T5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@Med]/[@[Med hours]]),""0"")"
Range("U5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@Large]/[@[Large hours]]),""0"")"
Range("V5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM(100/[@[mixed rated]])*[@[acutal ]],""No hrs or units"")"
Range("W5").Select
ActiveCell.FormulaR1C1 = _
"=SUM([@Small])/('Hrs + Units in each LC summary'!R4C22*[@[LC Multiplier]])"
Range("X5").Select
ActiveCell.FormulaR1C1 = _
"=SUM([@Med])/('Hrs + Units in each LC summary'!R4C22*[@[LC Multiplier]])"
Range("Y5").Select
ActiveCell.FormulaR1C1 = _
"=SUM([@Large])/('Hrs + Units in each LC summary'!R4C22*[@[LC Multiplier]])"
Range("Z5").Select
ActiveCell.FormulaR1C1 = "=SUM(Q1_[@:[l]])"
Range("AA5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(([@[ET Units Total]])/(SUM(Q1_[@:[l]])),""No units or hrs"")"


Range("AC5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[3])"
Range("AD5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Trans Total'!C[-25],'Trans Total'!C2,[@ID]),""0"")"
Range("AE5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Trans Total'!C[-25],'Trans Total'!C2,[@ID]),""0"")"
Range("AF5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Trans Total'!C[-25],'Trans Total'!C2,[@ID]),""0"")"
Range("AG5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[3])"
Range("AH5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Trans Total'!C12,'Trans Total'!C2,[@ID]),""0"")"
Range("AI5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Trans Total'!C14,'Trans Total'!C2,[@ID]),""0"")"
Range("AJ5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('Trans Total'!C16,'Trans Total'!C2,[@ID]),""0"")"
Range("AK5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R5C22*[@[LC Multiplier]])"
Range("AL5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R5C23*[@[LC Multiplier]])"
Range("AM5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R5C24*[@[LC Multiplier]])"
Range("AN5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Range("AO5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[Multi units total]]/[@[total hours]]),""nothing"")"
Range("AP5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[Multi units total]]/[@[Multi hours on task]]),""nothing"")"
Range("AQ5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@[s units]]/[@[s hrs]]),""nothing"")"
Range("AR5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@[m units]]/[@[m hrs]]),""nothing"")"
Range("AS5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@[l units]]/[@[l hrs ]]),""nothing"")"
Range("AT5").Select
ActiveCell.FormulaR1C1 = _
"=(SUM([@[ET Units Total]]+[@[Multi units total]]+[@[Single units total]]+[@[SIOC units total]]))/(SUM([@[total hours24]]+[@[total hours9]]+[@[total hours]]+[@total]))"
Range("AU5").Select
ActiveCell.FormulaR1C1 = _
"=(SUM([@Small]+[@[s units]]+[@[s units3]]+[@[s units18]]))/(SUM([@[expected rate225]]+[@[expected rate210]]+[@[expected rate2]]+[@[Expected ET RATE]]))"
Range("AV5").Select
ActiveCell.FormulaR1C1 = _
"=(SUM([@Med]+[@[m units]]+[@[m units4]]+[@[m units19]]))/(SUM([@[total rate26]]+[@[total rate11]]+[@[total rate]]+[@[Sum of hrs from LC data]]))"
Range("AW5").Select
ActiveCell.FormulaR1C1 = _
"=(SUM([@Large]+[@[l units]]+[@[l units5]]+[@[l units20]]))/(SUM([@[sm rate27]]+[@[sm rate12]]+[@[sm rate]]+[@[Multi hours on task]]))"
Range("AX5").Select
ActiveCell.FormulaR1C1 = _
"=(SUM([@[ET Units Total]]+[@[Multi units total]]+[@[Single units total]]+[@[s units18]]))/(SUM([@[SIOC hours on task2]]+[@[Single hours on task2]]+[@[Multi hours on task]]+[@[ET Hours on task]]))"
Range("AY5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[3])"
Range("AZ5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('RF Total'!C5,'RF Total'!C2,[@ID]),""0"")"
Range("BA5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('RF Total'!C6,'RF Total'!C2,[@ID]),""0"")"
Range("BB5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('RF Total'!C7,'RF Total'!C2,[@ID]),""0"")"
Range("BC5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[3])"
Range("BD5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('RF Total'!C12,'RF Total'!C2,[@ID]),""0"")"
Range("BE5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('RF Total'!C14,'RF Total'!C2,[@ID]),""0"")"
Range("BF5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('RF Total'!C16,'RF Total'!C2,[@ID]),""0"")"
Range("BG5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R6C22*[@[LC Multiplier]])"
Range("BH5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R6C23*[@[LC Multiplier]])"
Range("BI5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R6C24*[@[LC Multiplier]])"
Range("BJ5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Range("BK5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[Single units total]]/[@[total hours9]]),""nothing"")"
Range("BL5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[Single units total]]/[@[Single hours on task2]]),""nothing"")"
Range("BM5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@[s units3]]/[@[s hrs3]]),""nothing"")"
Range("BN5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUM([@[m units4]]/[@[m hrs4]]),""nothing"")"
Range("BO5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[l units5]]/[@[l hrs 2]]),""nothing"")"
Range("BP5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[3])"
Range("BQ5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('SIOC Total'!C5,'SIOC Total'!C2,[@ID]),""0"")"
Range("BR5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('SIOC Total'!C6,'SIOC Total'!C2,[@ID]),""0"")"
Range("BS5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('SIOC Total'!C7,'SIOC Total'!C2,[@ID]),""0"")"
Range("BT5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[3])"
Range("BU5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('SIOC Total'!C12,'SIOC Total'!C2,[@ID]),""0"")"
Range("BV5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('SIOC Total'!C14,'SIOC Total'!C2,[@ID]),""0"")"
Range("BW5").Select
ActiveCell.FormulaR1C1 = "=IFERROR(SUMIFS('SIOC Total'!C16,'SIOC Total'!C2,[@ID]),""0"")"
Range("BX5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R7C22*[@[LC Multiplier]])"
Range("BY5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R7C23*[@[LC Multiplier]])"
Range("BZ5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[-3])/('Hrs + Units in each LC summary'!R7C24*[@[LC Multiplier]])"
Range("CA5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Range("CB5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[SIOC units total]]/[@[total hours24]]),""nothing"")"
Range("CC5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[SIOC units total]]/[@[SIOC hours on task2]]),""nothing"")"
Range("CD5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[s units18]]/[@[s hrs315]]),""nothing"")"
Range("CE5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[m units19]]/[@[m hrs416]]),""nothing"")"
Range("CF5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUM([@[l units20]]/[@[l hrs 17]]),""nothing"")"
Range("AU5").Select
ActiveCell.FormulaR1C1 = _
"=(SUM([@Small]+[@[s units]]+[@[s units3]]+[@[s units18]]))/(SUM([@[sm hours]]+[@s]+[@[sm hours6]]+[@[sm hours21]]))"
Range("AU5").Select
Selection.AutoFill Destination:=Range("AU5:AW5"), Type:=xlFillDefault
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Solved, the column named ID had swapped names and I hadn't noticed! That's why it worked in one workbook and not the other, causing an object error :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
219
glaccounting
G
A
  • Question
Filter by Date macro
Replies
6
Views
510
kevin9999
K
W
  • Question
Defined coloring of dynamic diagrams
Replies
0
Views
228
WalmitAal
W
B
  • Question
i'm new to macros; i recorded this one that doesn't work. please help!
Replies
2
Views
506
Fluff
Fluff
Z
  • Question
Define Last Row as per the report
Replies
1
Views
559
bstory84
bstory84

Forum statistics

Threads
1,215,024
Messages
6,122,729
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top