Dim lastrow As Long not working?

_eNVy_

Board Regular
Joined
Feb 9, 2018
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
Code:
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
 
Upvote 0
Solution
Thanks, I used the following argument :

Code:
       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!
 
Upvote 0
Try

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

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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