Marco to update sheet first and then copy

[chuttus]

Hi,

I'm looking for a macro, that first updates a sheet located in a different file and then copies the updated data to paste in a report file. Below is a macro that I have got so far, but this macro copies data as is without updating Raw data sheet. The Raw data sheet is updated using macro button.

Any help greatly appreciated. Thanks!

Sub GetRaw()

Dim wb As Workbook
Dim activeWB As Workbook
Dim FilePath As String
Set activeWB = Application.ActiveWorkbook
Dim lastrow As Long

   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   Application.AskToUpdateLinks = False

'Update Report sheet by copying updated data from Raw
    Sheets("Report 1").Select
    Columns("A:K").Select
    Selection.Delete Shift:=xlToLeft
    
FilePath = "\\C:\ Data\Raw Data Lookup.xlsm"
   
   Set wb = Application.Workbooks.Open(FilePath, ReadOnly:=True)
    Sheets("Raw1").Select
    Columns("A:K").Select
    Selection.Copy
    activeWB.Activate
    Worksheets("Report").Activate
    Range("A1").Select
    ActiveSheet.Paste
   wb.Close False

'Breaks all links from importing sheets
Dim Links As Variant
Dim i As Long
Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
For i = 1 To UBound(Links)
ActiveWorkbook.BreakLink _
    Name:=Links(i), _
    Type:=xlLinkTypeExcelLinks
Next i

   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   Application.AskToUpdateLinks = True

[Roel Jongman]

All your macro does is open the raw data sheet and immediately copy the data. I do not see any prove that the raw data is updated. You say raw data sheet is updated with a macro button.

my guess would be you will have to trigger the macro in the raw data sheet "Raw Data Lookup" to update before you copy.
I do not know the name of that macro but you can call the macro to action in your code.

I put a line in red where you should call the macro
I also cleanup your code from not needed selects and activates.

Sub GetRaw()

Dim wb As Workbook
Dim activeWB As Workbook
Dim FilePath As String
Set activeWB = Application.ActiveWorkbook
Dim lastrow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

    'Update Report sheet by copying updated data from Raw
    Sheets("Report 1").Columns("A:K").Delete Shift:=xlToLeft
    
    FilePath = "\\C:\ Data\Raw Data Lookup.xlsm"
    Set wb = Application.Workbooks.Open(FilePath, ReadOnly:=True)
     Call ....   'put name update macro from opened file here (replace the dots with name)
     Sheets("Raw1").Columns("A:K").Copy activeWB.Worksheets("Report").Range("A1")
    wb.Close False

    'Breaks all links from importing sheets
    Dim Links As Variant
    Dim i As Long
    Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    For i = 1 To UBound(Links)
    ActiveWorkbook.BreakLink _
        Name:=Links(i), _
        Type:=xlLinkTypeExcelLinks
    Next i

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

[chuttus]

You are correct I want to trigger a macro in the "Raw Data Lookup" sheet before I copy the data. I tried your code but its giving me an error, the macro name called out is "UpdateMain" and this is how I called it out...

 Call UpdateMain

[Roel Jongman]

Ah yes.. have to call a different way for macro's in other workbook..
try the code below

Sub GetRaw()

Dim wb As Workbook
Dim activeWB As Workbook
Dim FilePath As String
Set activeWB = Application.ActiveWorkbook
Dim lastrow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

    'Update Report sheet by copying updated data from Raw
    Sheets("Report 1").Columns("A:K").Delete Shift:=xlToLeft
    
    FilePath = "\\C:\ Data\Raw Data Lookup.xlsm"
    Set wb = Application.Workbooks.Open(FilePath, ReadOnly:=True)
     Application.Run ("'Raw Data Lookup.xlsm'!UpdateMain") 
     Sheets("Raw1").Columns("A:K").Copy activeWB.Worksheets("Report").Range("A1")
    wb.Close False

    'Breaks all links from importing sheets
    Dim Links As Variant
    Dim i As Long
    Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    For i = 1 To UBound(Links)
    ActiveWorkbook.BreakLink _
        Name:=Links(i), _
        Type:=xlLinkTypeExcelLinks
    Next i

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

[chuttus]

Awesome, it worked . It takes longer to run the report though, so it proves that it is triggering the macro in other workbook.

Thank you!!