Copy data from open workbooks

[Inactiver183192]

Hi all first post!

I'm still learning but this one is probably an easy one. I just want to copy a dynamic range from all open workbooks (or windows? I still get a bit confused with which one to use) and then create a new worksheet in the one file "Datadump" and copy that data to it. So each open workbook data is now in its own worksheet in the one workbook. I was hoping to scroll through the open workbooks but I'm obviously missing something.

My second query was regarding the bypassing of the clipboard when you copy paste. Is there a way to do for a destination that will be newly created? I'd appreciate any help thank you!

Sub ImportData()
Dim WBnum As Integer
Dim WB As Workbook
 

For Each WB In Workbooks
If WB.Name <> "Datadump.xls" Then
    Range("B1", Range("B1").End(xlToRight)).Copy
    Workbooks("Datadump.xls").Worksheets.Add
    Range("B20").PasteSpecial
 
End If
Next WB

 

[Inactiver183192]

Anyone? Do I need to provide further info? Cheers

 

[wigi]

You're looping through workbooks, which is good, but for instance this line of code has no reference to the correct workbook and worksheet:

Range("B1", Range("B1").End(xlToRight)).Copy

If you do not specify these, the active workbook and worksheet will be taken...

 

[Cindy Ellis]

Hello Dixon123, and welcome to forum!

The following code, modified from what you posted, copies the data into an array, then into the newly-added target worksheet, avoiding the clipboard (as mentioned in your post).

Sub ImportData()
'copies data into an array rather than the clipboard, 
'      then puts it into a new sheet
Dim WB As Workbook
Dim vArray()
Dim LastCol As Long
 
For Each WB In Workbooks
    If WB.Name <> "DataDump.xls" Then
        With WB.Worksheets("Sheet1")
            LastCol = .Range("B1").End(xlToRight).Column
            vArray = .Range(.Cells(1, 2), .Cells(1, LastCol)).Value
        End With
        Workbooks("Datadump.xls").Worksheets.Add
 
        ActiveSheet.Range(Cells(20, 2), Cells(20, LastCol)).Value = vArray
 
    End If
Next WB
End Sub

There may be other or better ways to do this...but in my admittedly limited testing it worked OK.

Hope that helps,

Cindy

 

[Inactiver183192]

You're looping through workbooks, which is good, but for instance this line of code has no reference to the correct workbook and worksheet:

Range("B1", Range("B1").End(xlToRight)).Copy

If you do not specify these, the active workbook and worksheet will be taken...

hi! ah I see I didn't specify since the workbooks open would have different names each time and the worksheets won't have any specific worksheet names either. Cindy your code works a charm thank you!

 

[Inactiver183192]

Ok so I may have got a bit ahead of myself. The line

With WB.Worksheets("Sheet1")


is giving me issues if I want to delete all worksheets except for one called "Input". It gives me a subscript out of range error and I assume its to do something with the order and the fact that there will be only one worksheet to begin within in the datadump spreadsheet. If I changed Sheet1 to Input it still gives me an error. All the other data workbooks do have a worksheet named Sheet1. Any help again?

 

[Cindy Ellis]

I think I know what the problem is...the If statement excludes a file named "DataDump.xls", but I think your file is "Datadump.xls" without the extra capital D in the middle. In the IF statement, change the test to the exact spelling and capitalization of the file that will hold all of the new sheets, and let me know if that solved the problem.
Cindy

 

[Inactiver183192]

I think I know what the problem is...the If statement excludes a file named "DataDump.xls", but I think your file is "Datadump.xls" without the extra capital D in the middle. In the IF statement, change the test to the exact spelling and capitalization of the file that will hold all of the new sheets, and let me know if that solved the problem.
Cindy

of course! cheers Cindy appreciate it!