Run-time error '1004': in vba code

If Z = Q - T < 0 then "error"
Z = Q - T < 0 if Q < T
Q < T always, because 'Q' is calculated earlier than 'T', in the same column.
Then "Offset(Z + 1, 0)", then Z + 1 < 0
There are no negative rows in excel.

I was getting this error before I used what you mentioned above. I am using Z = Q - T to get the information pasted in tables on different sheets.
Can it be how I am selecting the information to be copied? because its not copying 1 row. has to have two rows to copy. One row with information doesn't error
simply doesn't copy. if no information is in row, it errors.


Private Sub CommandButton5_Click()

Dim DW As Worksheet
Dim WFIT As Worksheet
Dim EIT As Worksheet
Dim PIT As Worksheet
Dim BIT As Worksheet
Dim MIT As Worksheet
Dim N As Long
Dim M As Long
Dim O As Long
Dim Q As Long
Dim T As Long
Dim Z As Integer

Set DW = Worksheets("DailyWorksheet")
Set WFIT = Worksheets("WorkForceInformationTable")
Set EIT = Worksheets("EquipmentInformationTable")
Set PIT = Worksheets("ProjectInformationTable")
Set BIT = Worksheets("BidItemInformationTable")
Set MIT = Worksheets("MaterialInformationTable")

Application.ScreenUpdating = False

'Selects Cell C8 or Down 8 and Over 3

N = Cells(8, 3).End(xlDown).Row



DW.Range("C8:C" & N).Copy
Q = WFIT.Cells(1, 1).End(xlDown).Row
WFIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
T = WFIT.Cells(1, 1).End(xlDown).Row
Z = Q - T
DW.Range("B8:B" & N).Copy
WFIT.Cells(Rows.Count, 2).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("A8:A" & N).Copy
WFIT.Cells(Rows.Count, 3).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("V8:V" & N).Copy
WFIT.Cells(Rows.Count, 4).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("W8:W" & N).Copy
WFIT.Cells(Rows.Count, 5).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("X8:X" & N).Copy
WFIT.Cells(Rows.Count, 6).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues

'Contractor's Equipment Section

'Selects Cell G8 or Down 8 and Over 7

M = Cells(8, 7).End(xlDown).Row


DW.Range("G8:G" & M).Copy
Q = EIT.Cells(1, 1).End(xlDown).Row
EIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
T = EIT.Cells(1, 1).End(xlDown).Row
Z = Q - T
DW.Range("F8:F" & M).Copy
EIT.Cells(Rows.Count, 2).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("Y8:Y" & M).Copy
EIT.Cells(Rows.Count, 3).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("Z8:Z" & M).Copy
EIT.Cells(Rows.Count, 4).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("AA8:AA" & M).Copy
EIT.Cells(Rows.Count, 5).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues


'Bid Items Installed Section


'Selects Cell G19 or Down 19 and Over 7

O = Cells(19, 2).End(xlDown).Row

'If Range("A19").Value = "" Then


DW.Range("A19:A" & O).Copy
Q = BIT.Cells(1, 1).End(xlDown).Row
BIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
T = BIT.Cells(1, 1).End(xlDown).Row
Z = Q - T
DW.Range("B19:B" & O).Copy
BIT.Cells(Rows.Count, 2).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("D19:D" & O).Copy
BIT.Cells(Rows.Count, 3).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("E19:E" & O).Copy
BIT.Cells(Rows.Count, 4).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("V19:V" & O).Copy
BIT.Cells(Rows.Count, 5).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("W19:W" & O).Copy
BIT.Cells(Rows.Count, 6).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("X19:X" & O).Copy
BIT.Cells(Rows.Count, 7).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues


'Materials Used Section

'Selects Cell G19 or Down 19 and Over 7

P = Cells(19, 7).End(xlDown).Row


DW.Range("G19:G" & P).Copy
Q = MIT.Cells(1, 1).End(xlDown).Row
MIT.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
T = MIT.Cells(1, 1).End(xlDown).Row
Z = Q - T
DW.Range("F19:F" & P).Copy
MIT.Cells(Rows.Count, 2).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("J19:J" & P).Copy
MIT.Cells(Rows.Count, 3).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("Y19:Y" & P).Copy
MIT.Cells(Rows.Count, 4).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("Z19:Z" & P).Copy
MIT.Cells(Rows.Count, 5).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("AA19:AA" & P).Copy
MIT.Cells(Rows.Count, 6).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues
DW.Range("AB19:AB" & P).Copy
MIT.Cells(Rows.Count, 7).End(xlUp).Offset(Z + 1, 0).PasteSpecial xlPasteValues


'Project Information Section

Dim Owner As String, Contractor As String, ProjectZip As String, CityState As String, Weather As String, temp As String
Worksheets("dailyworksheet").Select
Owner = Range("C3")
ProjectTitle = Range("C4")
Contractor = Range("C5")
DDate = Range("G3")
ProjectNo = Range("G4")
ProjectZip = Range("G5")
CityState = Range("J3")
Weather = Range("J4")
temp = Range("J5")
Worksheets("ProjectInformationTable").Select
Worksheets("ProjectInformationTable").Range("A1").Select
If Worksheets("ProjectInformationTable").Range("A1").Offset(1, 0) <> "" Then
Worksheets("ProjectInformationTable").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Owner
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectTitle
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Contractor
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectNo
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectZip
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CityState
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Weather
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = temp
Worksheets("dailyworksheet").Select

'Summary of Construction Activities Section

Dim SOCA As String
Worksheets("dailyworksheet").Select
SummaryOfConstructionActivities = Range("A36")
ProjectTitle = Range("C4")
DDate = Range("G3")
ProjectNo = Range("G4")
Worksheets("SummaryInformationTable").Select
Worksheets("SummaryInformationTable").Range("A1").Select
If Worksheets("SummaryInformationTable").Range("A1").Offset(1, 0) <> "" Then
Worksheets("SummaryInformationTable").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = SummaryOfConstructionActivities
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectTitle
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProjectNo
ActiveCell.Offset(0, 1).Select
Worksheets("dailyworksheet").Select


Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

Ok, but, Q < T always, because 'Q' is calculated earlier than 'T' (before pasting new data), in the same column on the same sheet:

Please Login or Register  to view this content.

Try maybe to prepare and attach an example workbook with hypothetical data.

I have attached a copy of the spreadsheet.

Code is under the Update Daily Report button near A52 on DailyWorksheet

Try if it works:

Please Login or Register  to view this content.