Copying Data to New Workbook 1

[HairyHippy]

Hi

I'm trying to move data from multiple worksheets to a new workbook using VBA. I believe I therefore have to do this from each worksheet individually. Here is the code I'm using at the moment which is incorrect:

Workbooks("Old Book").Sheets("Sheet 1").Select
    Rows("1:1").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks("Old Book").Activate
    Workbooks("Old Book").Sheets("Sheet 2").Select
    Rows("3:3").Select
    Selection.Copy
    Windows("Book21").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select

The line that is wrong is "Windows("Book21").Activate". How do I substitute this to make Excel think it's adding the new data to the newly added work book? Or is there a far simpler way of performing this. Note the data is not always in the same place on each worksheet.

 

[Loomah]

Hi
Probably the easiest way is to create an object variable for your new book.

Before you do any of the copying declare a variable and assign the new workbook to it.
eg

Dim wbNew As Workbook
Set wbNew = Workbooks.Add

In this case you will then be able to refer to the workbook by using the variable
eg
wbNew.Activate
wbNew.Worksheets(2).Delete
and so on

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

Hi, again!
Actually, in this case you could do away with my first suggestion all together and get rid of most (if not all) of the selecting that is happening in your original code.

As a side issue it is not often necessary to activate of select workbook, worksheets ranges etc to perform tasks on them or to manipulate them.

Anyway, a modified version of your code.

Dim wbOld As Workbook
Set wbOld = ThisWorkbook    'Workbooks("Old Book")

With wbOld
    .Sheets("Sheet1").Rows("1:1").Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    .Sheets("Sheet2").Rows("3:3").Copy
            Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Range("A1").Select
End With
Application.CutCopyMode = False

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[HairyHippy]

Thanks - I'm going to use your first suggestion as the data that needs to be moved will move around (ie the row that I want to move to new workbook will move down a few rows each time as it's essentially a database that is getting written to all the time).