VBA Activating Sheet names

Hello.
I have this code in workbook. I am stating the sheets name to activate to jump back and forth. Is this necessary? I am looking for an easier way in case I change the sheet name and not be required to change the code. I hope I am clear.

Here is the code See bold lines
Workbooks.Add
    ChDir "N:\Syn"
   
    ActiveWorkbook.SaveAs Filename:="N:\Syn\bbbentpo.txt", FileFormat:=xlText, _
        CreateBackup:=False
 

   Windows("PullOrderbruce.xlsm").Activate

Sheets("output").Select
    Application.Goto Reference:="DIPO"
    Selection.Copy
    Windows("bbbentpo.txt").Activate    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
    ActiveWorkbook.SaveAs Filename:="N:\Syn\bbbentpo.txt", FileFormat:=xlText, _
        CreateBackup:=False
   
Windows("PullOrderbruce.xlsm").Activate
cebu1014Asked:
Who is Participating?
 
Rory ArchibaldCommented:
Sorry - that should be 2 lines:
wbk2.Activate    
Range("A1").Select

Open in new window

0
 
Rory ArchibaldCommented:
Assuming the correct workbook to start:
Dim wbk1 as workbook, wbk2 as workbook
set wbk1 = activeworkbook
set wbk2 = Workbooks.Add
    ChDir "N:\Syn"
    
    wbk2.SaveAs Filename:="N:\Syn\bbbentpo.txt", FileFormat:=xlText, _
        CreateBackup:=False
 

   wbk1.Activate

Sheets("output").Select
    Application.Goto Reference:="DIPO"
    Selection.Copy
    wbk2.Activate    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    wbk2.SaveAs Filename:="N:\Syn\bbbentpo.txt", FileFormat:=xlText, _
        CreateBackup:=False
    
wbk1.Activate

Open in new window


It is generally unnecessary to activate workbooks or sheets at all in order to manipulate them though.
0
 
zorvek (Kevin Jones)ConsultantCommented:
This should be all you need:

    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:="N:\Syn\bbbentpo.txt", FileFormat:=xlText, CreateBackup:=False
    Workbooks("PullOrderbruce.xlsm").Sheets("output").Range("DIPO").Copy
    With Workbooks("bbbentpo.txt")
        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .SaveAs Filename:="N:\Syn\bbbentpo.txt", FileFormat:=xlText, CreateBackup:=False
        .Close
    End With

Kevin
0
 
cebu1014Author Commented:
Rorya
You got the right idea. However, I get this message and and highlights line 15.
Here..

Wrong number of arguments or invalid property assignment.
It did add wbk2.

Thanks
0
 
cebu1014Author Commented:
THANKS FOR YOUR QUICK RESPONSE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.