Dim lastrow As Long not working?

_eNVy_

New Member
Joined
Feb 9, 2018
Messages
14
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
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,034
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
 

_eNVy_

New Member
Joined
Feb 9, 2018
Messages
14
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!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,331
Office Version
2013
Platform
Windows
Try

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

Forum statistics

Threads
1,086,237
Messages
5,388,636
Members
402,127
Latest member
Jemx

Some videos you may like

This Week's Hot Topics

Top