excel vba sum multi sheet got run-time error 1004: Application-defined or object-defined error

0
votes

I got this 1004 run-time error on the code below. The line I mark as comment is the key of this question. Whenever the line mark as comment this code always got 1004 run-time error. And the code works good when I put the line back as normal code. Is there anyway to skip the sheet activation during this code since that activation makes the excel jumping around during the code running.

Thank you.

Public Sub Summary_calculation()

   Dim wb As Workbook
   Dim ws As Worksheet
   Dim endrow As Integer
   Dim temprow As Integer
   Dim tempcol As Integer
   Dim i As Integer
   Dim totalrec As Integer
   Dim tmpSheet As Worksheet
   Dim SKUCol As Integer
   Dim SKUName As String
   Dim tmpSheetrow As Integer
   Dim tmpcol As Integer
   Dim tmpQty As Double

   Set wb = ActiveWorkbook
   Set ws = wb.Sheets("Summary")

   SKUCol = 3

   totalrec = ws.Cells(startrow, 1).CurrentRegion.Rows.Count - 1

   For i = 1 To totalrec

      SKUName = ws.Cells(i + startrow, SKUCol).Value

      Set tmpSheet = wb.Sheets(SKUName)

      'tmpSheet.Activate (Got 1004 error whenever remove this line of code)

      tmpSheetrow = tmpSheet.Cells(startrow, Gapcol).CurrentRegion.Rows.Count + startrow - 1

      For tmpcol = 0 To 16
         With wb.Sheets(SKUName)

            ws.Cells(i + startrow, tmpcol + 4).Value = Application.WorksheetFunction.Sum(.Range(Cells(startrow + 1, PropStartcol + tmpcol), Cells(tmpSheetrow, PropStartcol + tmpcol)))

         End With
      Next tmpcol
      ws.Cells(1, 10) = Format(i / totalrec, "00%")
      DoEvents
   Next i

End Sub
2
.netvbaexcelsumruntime-error

2 Answers

0
votes

Could you try :

  1. to replace Set wb = ActiveWorkbook by Set wb = ThisWorkbook
  2. to put your code in a new module

ThisWorkbook refers to the workbook where you execute your code. Error 1004 can happen when you have other workbook open.

0
votes

Instead of this:

ws.Cells(i + startrow, tmpcol + 4).Value = Application.WorksheetFunction.Sum(.Range(Cells(startrow + 1, PropStartcol + tmpcol), Cells(tmpSheetrow, PropStartcol + tmpcol)))

Write this:

ws.Cells(i + startrow, tmpcol + 4).Value = Application.WorksheetFunction.Sum(.Range(.Cells(startrow + 1, PropStartcol + tmpcol), .Cells(tmpSheetrow, PropStartcol + tmpcol)))

The difference is the dot in front of Cells (twice in the code). Without the dot, the Cells take as its worksheet the activesheet or the sheet in which they are located. And it breaks, because .Range() takes wb.Sheets(SKUName).

This is probably the most common problem in StackOverflow for :