1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Generic Statement on Excel VBA

Discussion in 'Software Development' started by mrxcel, Jul 10, 2007.

Thread Status:
Not open for further replies.
  1. mrxcel

    mrxcel Thread Starter

    Joined:
    Jul 10, 2007
    Messages:
    1
    Hello all,

    I am creating a Macro on Excel, that extracts the data from each tab and paste the data as text on a new Book. The problem I have is that when you run it VBA records the Book number like Book5 (if it was book 5 at the time) my case is Windows("Book11").Activate.
    I need a generic BookXX number or statement that tells excel it doesent matter what book it is. So If I run the Macro again and it happends to be Book26 it would do the same thing.
    Anybody can help on this?:confused:
    Here is a copy of a sample macro with the ERROR opens on book14 here.

    Sub asdf()
    '
    ' asdf Macro
    ' Macro recorded 7/10/2007 by
    '

    '
    Sheets("2007 IS").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=21
    Range("A54").Select
    Windows("TEST.xls").Activate
    Sheets("2008 IS").Select
    Range("A1:Q51").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book14").Activate
    Range("A55").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("D59").Select
    ActiveWindow.SmallScroll Down:=33
    End Sub
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Hi,

    I'm not sure I deciphered your goal from the macro, so I edited the code following my own thinking. Maybe you will like it, though.
    Code:
    Sub asdf()
    
        Dim NewBook As Workbook
        Workbooks.Add
        Set NewBook = ActiveWorkbook
        
        ThisWorkbook.Sheets("2007 IS").Activate
        Selection.Copy
        With NewBook.Sheets(1).Range("A1")
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipBlanks:=False, Transpose:=False
        End With
        
        ThisWorkbook.Sheets("2008 IS").Range("A1:Q51").Copy
        With NewBook.Sheets(1).Range("A55")
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipBlanks:=False, Transpose:=False
        End With
    End Sub
    Have fun with it :)

    Jimmy
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    When the new workbook is added it will be the "Active workbook" until the VBA code sets focus to a new workbook. All you have to do is set a variable equal to the workbook name after it is created and then refer to the variable name later in your code.

    Code:
    Workbooks.Add
    
    vName = ActiveWorkbook.Name
    
    'Add your code
    
    Workbooks(vName).Activate
    
    

    Regards,
    Rollin
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/594034

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice