Retroshift
Board Regular
- Joined
- Sep 20, 2016
- Messages
- 119
- Office Version
- 2019
- Platform
- Windows
Hi,
In my worksheet, I would like to add a macro which does the following at once: 1) refresh the active workbook, 2) clear the contents of a specific worksheet, 3) import columns data from a closed workbook, 4) refer to the path of the closed workbook based on cell data in the open workbook, 5) identify the columns data until the last row, and 6) add a messagebox.
Below is what I got so far but it is not complete. Does anyone know how to make the macro work? Many thanks in advance
In my worksheet, I would like to add a macro which does the following at once: 1) refresh the active workbook, 2) clear the contents of a specific worksheet, 3) import columns data from a closed workbook, 4) refer to the path of the closed workbook based on cell data in the open workbook, 5) identify the columns data until the last row, and 6) add a messagebox.
Below is what I got so far but it is not complete. Does anyone know how to make the macro work? Many thanks in advance
VBA Code:
Sub RefreshClearCopyColumnsData()
'First refresh the open workbook to update all the values
ActiveWorkbook.RefreshAll
'Then clear the contents of the worksheet in the open workbook
wkBk.Sheets("Sheet1").Cells.ClearContents
'Then get columns data from closed workbook into open workbook
Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object
Set myApp = CreateObject("Excel.Application")
'Get path and Closed Workbook filename from (indirect?) cell A5 in open workbook
Set wkBk = myApp.Workbooks.Open("[(indirect) path reference from sheet2!A5 in open workbook]")
'Specify the columns until the last row with data
lastRow = wkBk.Sheets(1).Range("A,O,R,V,AD,AG,AH" & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range("A1:A,O1:O,R1:R,V1:V,AD1:AD,AG1:AG,AH1:AH" & lastRow).Copy
myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("Sheet1")
wkSht.Activate
Range("C2").Select
wkSht.Paste
Exit Sub
'Add messagebox
MsgBox “Data successfully imported”
End Sub
