CODEWITHSUNDEEP
vba
Lysander2001
Lysander2001

Reputation: 51

VBA: Having trouble counting and then printing the no. of rows- error 1004

In one workbook, I have sales data. I want to count the number of rows (start from row 6 as rows 1-5 are blank) and then paste this into my other workbook. They're both already open and always will be during this process.The below code is returning error 1004

Sub StoreDateAndRowCount()
Dim SalesWb As Workbook, TrackerWb As Workbook
Set SalesWb = Workbooks("Trial 30.08.2021.xlsm")
Set TrackerWb = Workbooks("Tracker.xlsm")
Dim SalesWs As Worksheet, TrackerWs As Worksheet
Set SalesWs = SalesWb.Sheets("Sales Volumes")
Set TrackerWs = TrackerWb.Sheets("Tracker ET")
Dim last_row As Long
       Dim Date1 As Long
       LDate = Date
       'Below is where I'm getting the error
       'Application- defined or Object- defined error 1004 
 last_row = SalesWs.Cells(Rows.Count, 6).End(xlUp).Row
            TrackerWs.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = last_row
           TrackerWs.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = LDate    
    End Sub

Upvotes: 1

Views: 62

Answers (1)

HackSlash
HackSlash

Reputation: 5803

Always use fully qualified references. When you call Rows.Count it's going to pull from ActiveWorkSheet which is not going to be the same as SalesWs and TrackerWs.

You might be getting that error because the workbook you are citing isn't open.

Option Explicit

Public Sub StoreDateAndRowCount()
    Dim SalesWb As Workbook
    Set SalesWb = Workbooks("Trial 30.08.2021.xlsm")
    Dim TrackerWb As Workbook
    Set TrackerWb = Workbooks("Tracker.xlsm")
    Dim SalesWs As Worksheet
    Set SalesWs = SalesWb.Sheets("Sales Volumes")
    Dim TrackerWs As Worksheet
    Set TrackerWs = TrackerWb.Sheets("Tracker ET")
    
    Dim LDate As Long
    LDate = Date
    
    Dim last_row As Long
    last_row = SalesWs.Cells(SalesWs.Rows.Count, 6).End(xlUp).Row
    TrackerWs.Range("B" & TrackerWs.Rows.Count).End(xlUp).Offset(1).Value = last_row
    TrackerWs.Range("A" & TrackerWs.Rows.Count).End(xlUp).Offset(1).Value = LDate
End Sub

Upvotes: 1

Related Questions