File name issue with VBA

[pure vito]

Hi all,

I have an issue i'm not sure how to resolve,

firstly i send out this document for others to use and then they send it back to me, the issue arises when the user sends the file back under a different name,

it stops this code from working because of these lines in the code "Windows("New Pick Form.xlsm").Activate" is there a way around this issue, its basically the name change that's causing the problem?

thanks in advance,

Sub mac1pasteboh()
'
' mac1pasteboh Macro
'

'
    Windows("L461 PP1 BOH.xlsm").Activate
    Sheets("LSA").Select
    
    Windows("New Pick Form.xlsm").Activate
    Sheets("Sheet1").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("L461 PP1 BOH.xlsm").Activate
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("A:A").Find("").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("New Pick Form.xlsm").Activate
    Range("B4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("L461 PP1 BOH.xlsm").Activate
    Range("B2").Select
    Selection.End(xlDown).Select
    Range("B:B").Find("").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("New Pick Form.xlsm").Activate
    Range("F4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("L461 PP1 BOH.xlsm").Activate
    Range("F2").Select
    Selection.End(xlDown).Select
    Range("F:F").Find("").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O2").Select
    Selection.End(xlDown).Select
    Range("O:O").Find("").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("New Pick Form.xlsm").Activate
    Range("H3").Select
    Application.CutCopyMode = False
End Sub

 

[Joe4]

Is "New Pick Form.xlsm" supposed to be the name of the file itself, containing the code?

If so, you can easily capture the name dynamically at the beginning of your code, and easily reference it later in your code.
I would do this through a workbook variable.

So put this at the very beginning of your code:

Dim wb as Workbook
Set wb = ActiveWorkbook

Then you can replace all instances of:

Windows("New Pick Form.xlsm").Activate

in your code with:

wb.Activate

 

[pure vito]

That's absolutely fantastic thank you so very much for taking the time, this may have seemed an easy task for you but i'm very new to this so thanks again.

 

[Joe4]

That's absolutely fantastic thank you so very much for taking the time, this may have seemed an easy task for you but i'm very new to this so thanks again.

No worries! Glad I was able to help!

Using object variables is not something I would expect noob VBA code writers to know (I know it took me a few years myself before I started using them).
As the saying goes "its easy when you know how".