Dim lastrow As Long not working?

[_eNVy_]

Can anyone help please?

It looks right to me but when the macro finishes, it pastes my formula in AC1:AC3.
There are breaks in the data however, by pressing Ctrl and End it takes me to cell AC520.

Sub ConsolidateTimeSheets()   Dim Wbk As Workbook
   Dim ws As Worksheet, wsData As Worksheet
   Dim Fname As String, Pth As String
   Dim Rw As Long
   Dim lastrow As Long
   
   'wsData needs to be set to the name of RawData (where the Timesheets are being pulled into).
   Set wsData = Workbooks("DataDump_v3.xlsb").Sheets("RawData")
   'Rw is the RowNumber where the start of the process needs to begin.
   Rw = 2
   'Pth is the directory of where the Timesheet are held
   Pth = "C:\Users\040428\Desktop\LiamG\Excel_development_work_for_Brick_by_Brick_\Timesheets\"
   'Fname is the directory and the file extension but with a wild card either side of the extension to grab the filename and
   'alternative file extensions
   Fname = Dir(Pth & "*.xls*")
   'lastrow is a process to identify what is the last row of data
   lastrow = Range("B" & Rows.Count).End(xlUp).Row
   
   '===============================================================================================================================
   '===============================================================================================================================
   '===============================================================================================================================
   Do While Fname <> ""
      Set Wbk = Workbooks.Open(Pth & Fname)
      For Each ws In Wbk.Worksheets
         If ws.Visible = xlSheetVisible Then
            wsData.Range("D" & Rw).Resize(19, 5).Value = ws.Range("C17:G33").Value
            wsData.Range("A" & Rw & ":A" & wsData.Range("D" & Rows.Count).End(xlUp).Row).Value = ws.Range("D3").Value
            wsData.Range("B" & Rw & ":B" & wsData.Range("D" & Rows.Count).End(xlUp).Row).Value = ws.Range("D9").Value
            wsData.Range("C" & Rw & ":C" & wsData.Range("D" & Rows.Count).End(xlUp).Row).Value = ws.Range("D7").Value
            wsData.Range("I" & Rw & ":AB" & wsData.Range("D" & Rows.Count).End(xlUp).Row).Value = ws.Range("I17:AB33").Value
            Rw = Rw + 20
         End If
      Next ws
      Wbk.Close False
      Fname = Dir()
   Loop
   
   '===============================================================================================================================
   '===============================================================================================================================
   '===============================================================================================================================
   
   Range("AC2") = "=sum(i2:ab2)"
   Range("AC2").Copy _
   Destination:=Range("AC3:AC" & lastrow)
   
   
End Sub

 

[baitmaster]

Lastrow is the last used row in column B - if this were an empty column then you'd be pasting right at the top of your worksheet

Using Ctrl + End is not ideal either, Excel easily gets confused about the end of a worksheet

A better way of finding the last used row of the worksheet is a function like this. Pass a worksheet to it and it tells you the last row that contains any data or formula

Function lastUsedRow(ws As Worksheet) As Long

On Error Resume Next
    lastUsedRow = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
On Error GoTo 0


End Function

 

[_eNVy_]

Thanks, I used the following argument :

       lRow = Cells.Find(What:="*", _                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
      
   Range("AC2") = "=sum(i2:ab2)"
   Range("AC2").Copy _
   Destination:=Range("AC3:AC" & lRow)

Thanks!

 

[Michael M]

Try

Sub MM1()
Dim lr As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
Range("AC2:AC" & lr) = "=sum(i2:ab2)"
End Sub