0

I have a workbook with a list of ISO-codes (from A2 till A138) De cell A2 is selected. In the vba code I ask to open a template run the auto open macro (get data - save as and close this file) and then in the initial file I want to delete row 2 and repeat the actions until we are at the end of the list. But the vba stops when the template did the save as and close the file. What do I have to do to continue the vba in the initial file ? Thanks for helping !

this is the VBA in the initial file :

Sub Scorecalculation2()

Dim ThisWkbkNm As String
ThisWkbkNm = ActiveWorkbook.Name

' Select cell A2, *first line of data*.
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.

Do Until IsEmpty(ActiveCell)

ChDir "M:\RM\Country Risk\Data\MacroFiches\CRAM templates and critical values"
Workbooks.Open Filename:= _
"M:\RM\Country Risk\Data\MacroFiches\CRAM templates and critical values\RISK_score_OECD - CRAM_PROTOTYPEtest.xlsm" _
, UpdateLinks:=3

Workbooks(ThisWkbkNm).Activate
Rows(2).EntireRow.Delete
Loop
End Sub

I created two vba procedures

  • one for opening the template - run the auto open - save as and close the file
  • one for deleting the row 2 and run them each time manually....
BigBen
  • 46,229
  • 7
  • 24
  • 40
NDWA
  • 1
  • "...when the template did the save as and close the file..." Where are you saving and closing the file in the sample code you provided? Side note, you should [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) (and Activate) in your code – cybernetic.nomad Apr 13 '23 at 14:11
  • the code to save and close the file is in the second file – NDWA Apr 13 '23 at 14:52
  • 1
    It's not clear why you open the same xlsm file over and over? It seems likely you'd be better off structuring this differently. – Tim Williams Apr 13 '23 at 15:47
  • because thats the template and each time new data will filled in. It depends on the iso code. – NDWA Apr 14 '23 at 11:57

0 Answers0