Hi All
I have a macro to import data to a sheet which is triggered from within another macro.
I can execute the macro on its own and it imports the data as expected but when running the sequence it is not importing the data.
All the macros are in the same work book and in the same module. Have tried different modules which has not worked either.
This is my code
Any assistance will be greatly appreciated
Adriaan
I have a macro to import data to a sheet which is triggered from within another macro.
I can execute the macro on its own and it imports the data as expected but when running the sequence it is not importing the data.
All the macros are in the same work book and in the same module. Have tried different modules which has not worked either.
This is my code
VBA Code:
Sub ClearSheets()
Worksheets("RSR").Range("A2:R100").Clear
PullClosedDataRSR_CHPP
End Sub
Sub PullClosedDataRSR_CHPP()
Dim filePath As String
Dim SourceWb As Workbook
Dim TargetWb As Workbook
Set TargetWb = ActiveWorkbook
Dim lrTarget As Long
lrTarget = TargetWb.Sheets("RSR").UsedRange.Rows.Count
filePath = TargetWb.Sheets("System").Range("A2").Value
Set SourceWb = Workbooks.Open(filePath)
Dim lrSource As Long
lrSource = SourceWb.ActiveSheet.UsedRange.Rows.Count
SourceWb.Sheets("Sheet1").Range("A2:R" & lrSource).Copy Destination:=TargetWb.Sheets("RSR").Range("A2")
SourceWb.Close savechanges:=False
TargetWb.Sheets("RSR").Select
If lrTarget > lrSource Then
Application.DisplayAlerts = False
TargetWb.Sheets("RSR").Range(Cells(lrSource + 1, "A"), Cells(lrTarget, "A")).EntireRow.Delete
Application.DisplayAlerts = True
Else
End If
AttachWorkbookIntoEmailMessage
End Sub
Sub AttachWorkbookIntoEmailMessage()
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.To = "[EMAIL]Adriaan.debeer@angloamerican.com[/EMAIL]"
.Subject = "CHPP RSR WK1" & " " & Date & " " & Time
.Body = "Find attached next weeks resource requirements. Please confirm all resources are available for next weeks execaution "
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
Any assistance will be greatly appreciated
Adriaan
Last edited by a moderator: