last row modification

[ysherriff]

To all,

Can someone help me with a more efficient way to use the last row statement. I keep havign to change the destination cell number everytime if i use a different workbook or parameter. The issue i am having is highlighed in red.

LastRow = ActiveSheet.Range("D94").End(xlUp).Row
If LastRow > 1 Then
sh.Range("B9:L" & LastRow).Copy

'activate generator workbook
currentWB.Activate

'activate master worksheet
TargetSh.Activate

TargetSh.Range(DestCell.Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DestCell = DestCell.Offset(LastRow - 10)
dataWB.Close False

 

[Smallman]

Ysherrif

What is destcell? It is like reading a book with pages torn out when you don't show the bigger picture.

Smallman

 

[ysherriff]

I apologize. What an idiot. I am. Here is the full code.

Sub mod_consolidate_multi_wkbk()
Dim strListSheet As String, sh As Worksheet, TargetSh As Worksheet
Dim DestCell As Range, LastRow As Long, i As Integer, strFileNamePath As String
Dim strFileName As String, currentWB As Workbook, dataWB As Workbook, filecount As Integer
Dim prctProgress As Single

strListSheet = "Report File Path"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

filecount = Range("FILE_COUNT_LEVEL") ' the number of files to consolidate

On Error Resume Next
Set TargetSh = Worksheets("Master")
On Error GoTo 0

Sheets("Master").Activate
Rows("2:" & Rows.Count).ClearContents

Set DestCell = TargetSh.Range("A1")
Set DestCell = DestCell.Offset(1, 0)

'On Error GoTo ErrH
Sheets(strListSheet).Activate
Range("b2").Select

ProgressBox.Show 'displays progress bar

'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
For i = 1 To filecount

strFileNamePath = Range("strFileName").Offset(i, 1)
strFileName = Range("strFileName").Offset(i, 0)

'this displays the status in percentage value in the progress bar of the PSR file being generated and the name of file
'being generated
Application.StatusBar = "Generating " & strFileName & " Consolidation....." & i & " of " & filecount

prctProgress = i / filecount * 100

ProgressBox.Increment prctProgress, "Consolidating for " & strFileName & "- " & i & " out of " & filecount


Application.Workbooks.Open strFileNamePath, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
ActiveSheet.Unprotect "ops"
Set sh = ActiveSheet
ActiveSheet.AutoFilterMode = False

'select usedrange to copy
LastRow = ActiveSheet.Range("D94").End(xlUp).Row
If LastRow > 1 Then
sh.Range("B9:L" & LastRow).Copy

'activate generator workbook
currentWB.Activate

'activate master worksheet
TargetSh.Activate

TargetSh.Range(DestCell.Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DestCell = DestCell.Offset(LastRow - 8)
dataWB.Close False
End If




Next i
Application.StatusBar = False
ProgressBox.Hide
currentWB.Activate
Sheets("Master").Activate
ActiveSheet.UsedRange.EntireColumn.AutoFit 'AutoFit the column width
Columns("E:E").Select
Selection.Style = "Percent"
MsgBox "Reports have been generated succussfully!", vbInformation

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub