picking up workbook name in VBA

[Yokiboha]

I have a workbook with vba code that refers in many places to the name of the workbook.

I need to save the workbook with a different name but when I do I have to change the name in the code to the new name.

For exaple a book is named aaa.xls

I save it as bbb.xls

a part of the code reads:

Sheets("StockIn").Select
With Workbooks("aaa.xls").Sheets("StockIn"......

I need to change this so that the "aaa.xls" is the workbook name so that when the workbook is saved as "bbb.xls" I don'y have to change anything and the code will run.

Does that make sense? If so can anybody help?

 

[Fazza]

Hi,

Maybe in VBA do a change all (CTRL-H) of "Workbooks("aaa.xls")" to ThisWorkbook

Regards, Fazza

 

[Norie]

Why refer to the workbook with a hardcoded name?

Perhaps you could use ThisWorkbook which will refer to the workbook the code is in.

 

[Yokiboha]

Hi guys, thanks for the replies, I have tried that but can't get it to work, comes up with type mismatch

 

[Kenneth Hobson]

Without seeing your code, we can't troubleshoot.

Sub ShowWBName()
  MsgBox ThisWorkbook.FullName, , ThisWorkbook.Name
End Sub

 

[Norie]

Can you post the code where you get the error?

 

[Yokiboha]

Sub Delrows()


With Workbooks(ThisWorkbook).Sheets("Salesdata")
        With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            .AutoFilter Field:=1, Criteria1:="x"
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
    End With


End Sub

 

[Norie]

I don't think anybody suggested using it like that.

Though I'll admit we didn't give specifics but that's hard to do without more information.

With ThisWorkbook.Sheets("Salesdata")

 

[Yokiboha]

Hi Norie, many thanks...it works fine now