Generic Statement on Excel VBA

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
Joined
Jul 28, 2006
Messages
1,225
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
 
Joined
Sep 4, 2003
Messages
4,916
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top