# Generic Statement on Excel VBA



## mrxcel (Jul 10, 2007)

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? 
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


----------



## Jimmy the Hand (Jul 28, 2006)

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.

```
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


----------



## Rollin_Again (Sep 4, 2003)

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.


```
Workbooks.Add

vName = ActiveWorkbook.Name

'Add your code

Workbooks(vName).Activate
```
Regards,
Rollin


----------

