Option Explicit
Private Sub CommandButton1_Click()
Dim wbSource As Workbook
Dim wks As Worksheet
Dim wksSource As Worksheet
Dim strSourceShCodename As String
Dim strPath As String
Dim strSourceWBName As String
Dim lLRow As Long
Dim lLRowNegOffset As Long
Dim shtRecentIssues As Worksheet
'// Change to suit //
strSourceWBName = "Mouldings, Liquids & Powders Outstanding CA's.xls"
strPath = "T:\QA Shared Data\5 D Reports" & "\"
'// I used the sheets' codenames to locate them, rather than the sheets' tab names //
'// to eliminate issues with changes to a sheet's name (on the tab). //
strSourceShCodename = "shtMOULDINGS"
'// Just in case the desired wb isn't there. //
On Error Resume Next
Set wbSource = Workbooks.Open(strPath & strSourceWBName, , True)
On Error GoTo 0
If Not wbSource Is Nothing Then
'// Find the worksheet with the correct codename and set a reference to it. //
For Each wks In wbSource.Worksheets
If wks.CodeName = strSourceShCodename Then
Set wksSource = wks
Exit For
End If
Next
'// Ensure the sheet has not been deleted. //
If wksSource Is Nothing Then
MsgBox "Unable to find source sheet.", vbOKOnly + vbInformation, vbNullString
wbSource.Close False
Exit Sub
End If
Else
MsgBox "Unable to locate wb..", vbOKOnly Or vbInformation, vbNullString
Exit Sub
End If
With wksSource
'// I still wasn't sure if we are only grabbing the cells in L Col, but this //
'// should take the vals from the last five rows in L col and plunk them into //
'// the next available cells in col A of the destination wb. //
lLRow = .Cells(.Rows.Count, "L").End(xlUp).Row
lLRowNegOffset = Application.Max(2, lLRow - 4)
shtRecentIssues.Cells(shtRecentIssues.Rows.Count, "A").End(xlUp).Offset(1) _
.Resize(Range(.Cells(lLRowNegOffset, "L"), .Cells(lLRow, "L")).Rows.Count).Value _
= Range(.Cells(lLRowNegOffset, "L"), .Cells(lLRow, "L")).Value
.Parent.Close False
End With
End Sub
