Excel VBA – Set Filename as Text within specific column of active row

enter image description here

I am trying to write a VBA code that will save my workbook, with the filename being the address shown in the H column of another workbook. I want it to take the address from only the active row of that workbook. I’m confused because, at first, this code was working and then stopped working. Now it only takes the address from H10 (highlighted box) even when im not clicking it. It is 8 rows down from the active row so maybe thats why but i thought the 8 referred to H being the 8th column and this worked initially. How can i fix this? See below the code i have tried. This worked at first and now doesn’t for some reason.

`Sub CREATE_REINTSTATEMENT_SHEET()

    Dim wb As Workbook, wb2 As Workbook
'set source workbook - this is the planning sheet
    Set wb = ActiveWorkbook
'set workbook 2 as the new reinstatement sheet, taken from template
    Set wb2 = Workbooks.Add("filepath")

'Next we will save the new reinstatement sheet with the filename as the works address
    Dim Loc As String
    Dim FileSaveName As String
    Dim fPath As String
    Dim row As Integer
    row = ActiveCell.row
    Loc = wb.Worksheets("Sheet1").Cells(row, 8)
    fPath = "myfilepath\"
    wb2.SaveAs Filename:=fPath & Loc & ".xlsm", FileFormat:=52

it will come up with a warning if there’s already a file called this. If so, need to save as manually.

Thank you

>Solution :

You need to reference which workbook you want to use. In your code you do this:

'set workbook 2 as the new reinstatement sheet, taken from template
Set wb2 = Workbooks.Add("filepath")

Everytime you add a new workbook it becomes the active one by default. So then your code does this:

row = ActiveCell.row

It will get the row number of active cell from wb2

Try activating the workbook first or getting the Loc variable before. maybe something like this:

Sub CREATE_REINTSTATEMENT_SHEET()
Dim wb As Workbook, wb2 As Workbook
Dim Loc As String
Dim FileSaveName As String
Dim fPath As String
Dim row As Integer

Set wb = ActiveWorkbook
row = ActiveCell.row
Loc = wb.Worksheets("Sheet1").Cells(row, 8)
fPath = "myfilepath\"

Set wb2 = Workbooks.Add("filepath")
wb2.SaveAs Filename:=fPath & Loc & ".xlsm", FileFormat:=52

End Sub

Leave a Reply