MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
crossposted at: mrexcel.com/board/threads/macr…d-import-columns.1205668/
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. Can anyone help me out?
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