Copy all data from an Excel sheet in a different workbook to a blank sheet in current WB

Hi drjarmin

Not sure what your setup or actual filenames are but this will get you started...

Option Explicit

Sub GetStuff()
'Declare Variables
Dim wb1 As Workbook, wb2 As Workbook, ws As Worksheet
'Assign ref of ActiveWorkbook housing the code
Set wb1 = ThisWorkbook: Set ws = wb1.ActiveSheet
'Assign ref of Workbook to open
Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\" & "Tester.xlsx") 'Change Tester.xlsx to name of your workbook.
'Copy the information required
ActiveSheet.UsedRange.Copy
'Paste into ActiveWorkbook housing the code
ws.Activate
Range("A1").PasteSpecial xlPasteValues
'Clear Clipboard
Application.CutCopyMode = False
End Sub

Thank you, that worked a treat. 'UsedRange' was the solution.

Is there any way I can select the active range (the data I've selected) in the target workbook? Now I have 'UsedRange', I can apply a filter in the Target sheet (based on a value in column 1 of the table) and only copy that data rather than copying the whole sheet. Problem is I get an error trying to copy the data....

        Set wbkImportData = Workbooks.Open(Filename:=strImportPath & strImportFileName, UpdateLinks:=0)
        'Apply a filter to the data to select all rows with column 1 set to "ACME LTD"
        wbkImportData.ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:="ACME LTD"
        
        'Next two lines to select all the filtered data (<----------is there an easier way to select the results of the filter?)
        wbkImportData.ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
        wbkImportData.ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
        
        'Copy the selected data to paste back into the main workbook running the code
        wbkImportData.Selection.Copy   '<----------This line (and all other variants I've tried) throws up an 438 error. If I record a macro at this point, it uses selection.copy

        'return to main workbook and paste the data
        wbkThisWorkbook.Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False 'Stop prompt of large amount of data saved to clipboard....

        'Close workbook where the data came from
        Workbooks(strImportFileName).Close savechanges:=False

Not sure I follow....What Range do you want to copy and where do you want to paste it...

Edit

is there an easier way to select the results of the filter?

.SpecialCells(12).Copy

Perhaps you should upload a sample

....Delete.....

My target data is in multiple tables spread across many workbooks so I'm creating a loop to open each spreadsheet, copy the data and paste into its own sheet in my workbook. I've got this working since you posted to use the UsedRange range object.

But I don't need ALL the data in each table. I only need 1 or more rows which is based on a value in column 1. This is the same for every sheet I need to import. For examples sake, let's say this column is called 'Supplier Name' and I only want rows where the supplier is called 'ACME LTD'.

The attached diagram shows how the spreadsheet with the the data to be imported looks at each step of the process.

Step 1

I've modified my code so it opens up each target sheet

and applies an auto filter on the sheet and filters on column 1. So if I only wanted to import rows from 'Supplier Name' = 'ACME LTD' I run this code...

Step 2

wbkImportData.ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:="ACME LTD"

Step 3

After the filter is applied, I now have a table showing just the rows I want to copy and paste into my workbook as the auto filter will hide rows that don't meet the criteria. So my logic is I need to select all the 'visible' rows (use CTRL+Shift+Down, CTRL+Shift+End)...

 wbkImportData.ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
wbkImportData.ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select

Step 4 - Here is where I get stuck

When I run this, all the data I want is selected. I just need to copy this back into my main workbook. But the 'copy' method throws up an error

wbkImportData.Selection.Copy

<--------- I can't 'select' the data to paste it back without an error. This is the only line of code that doesn't work

If I can copy this data, I am set as my next code block activates my main workbook correctly and pastes it where I want it.

wbkThisWorkbook.Activate
ActiveSheet.Paste

Example.jpg

*Deleted* Managed to combine image with post above.

Pls upload a sample workbook and I will gladly help solve your problem...

Edited...This is what you need to achieve....Tx to bakerman2

With wbkImportData
    With .ActiveSheet.Cells(1).CurrentRegion
        .AutoFilter Field:=1, Criteria1:="ACME LTD"
        .SpecialCells(12).Copy wbkThisWorkbook.ActiveSheet.Range("A1")
    End With
    ActiveSheet.AutoFilterMode = False
End With

Here you go...

Option Explicit

Private Sub CommandButton1_Click()
Dim wbkThisWorkbook As Workbook, wbkImportData As Workbook
Dim ws As Worksheet
Dim strImportPath As String, strImportFileName As String
strImportPath = ThisWorkbook.Path
strImportFileName = "ImportData.xlsx"
Set wbkThisWorkbook = ThisWorkbook: Set ws = wbkThisWorkbook.ActiveSheet
Set wbkImportData = Workbooks.Open(Filename:=strImportPath & "\" & strImportFileName, UpdateLinks:=0)
With wbkImportData
    With .ActiveSheet.Cells(1).CurrentRegion
        .AutoFilter Field:=1, Criteria1:="ACME LTD"
        .SpecialCells(12).Copy wbkThisWorkbook.ActiveSheet.Range("A1")
    End With
    ActiveSheet.AutoFilterMode = False
End With
Workbooks(strImportFileName).Close savechanges:=False
Application.CutCopyMode = False
End Sub

It does work ... few examples...Glad i could help...Tx for rep +

Sub Selection_Copy()
Selection.Copy Sheet2.Range("A1")
End Sub

Sub Selection_Copy()
Selection.Copy
Sheet2.Range("A1").PasteSpecial xlPasteValues
End Sub

Sub Selection_Copy()
Selection.Copy Destination:=Sheet2.Range("A1")
End Sub

Ah, ok, so I guess my original code wasn't working because I didn't specify a destination to the copy method.

Still trying to get my head around this. Trying some other examples but not working. How would I modify this existing code, for example, copy all of column A with data in it (A1.....A100 or whatever the last cell with data is)?

I guess I can't use the .SpecialCells(12) property as this would select all the data and I only want column A. Also, I don't want to filter it as I want all values....

With wbkImportData
    With .ActiveSheet.Cells(1).CurrentRegion
        .AutoFilter Field:=1, Criteria1:="ACME LTD"
        .SpecialCells(12).Copy wbkThisWorkbook.ActiveSheet.Range("A1")
    End With
    ActiveSheet.AutoFilterMode = False
End With

Trying things like this but they just generate an Err=91 error:

wbkImportData.Sheets("Sheet1").Range("A1:A100").Copy wbkThisWorkbook.ActiveSheet.Range("A1")

Your code does work...

wbkImportData.Sheets("Sheet1").Range("A1:A100").Copy wbkThisWorkbook.ActiveSheet.Range("A1")

Try this

With wbkImportData
    With .ActiveSheet
        .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy wbkThisWorkbook.ActiveSheet.Range("A1")
    End With
End With

Best to start a new thread as your original query has been solved