?
Solved

VBA Activating Sheet names

Posted on 2011-04-27
5
Medium Priority
?
250 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:cebu1014
  • 2
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35476762
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35476819
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
 

Author Comment

by:cebu1014
ID: 35477009
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 35477020
Sorry - that should be 2 lines:
wbk2.Activate    
Range("A1").Select

Open in new window

0
 

Author Closing Comment

by:cebu1014
ID: 35477315
THANKS FOR YOUR QUICK RESPONSE
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question