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

[drjarmin]

Not sure why I'm finding this so hard....

Using VBA, I want to be able to import a whole sheet from another Workbook into my current spreadsheet. I know the path, filename and worksheet name of the target data.

I don't know how many rows / columns there are in the table in the target sheet so its fine to copy the whole sheet into a nominated sheet in my current Workbook. All examples I've found on Google assume you know the columns / rows of the target, or prompt the user to type that in. I just want ALL data in the target sheet as it varies.

Trying something like this but not able to do the import...

'Open the target spreadsheet
Set wbkImportData = Workbooks.Open(strTargetWorkbook)
wbkImportData.Sheets(strTargetSheet).Select
wbkImportData.ActiveSheet.Cells.Select
wbkImportData.Selection.Copy <------------ This line throws up an error

'### This next bit of code just doesn't work even if I manually (using ctrl+c) copy the target data to the clipboard
'In my current workbook, select a blank sheet to import the data
Sheets("ImportData").Select
Cells.Select
ActiveSheet.Paste

[sintek]

Hi drjarmin

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

Please Login or Register  to view this content.

[drjarmin]

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

Please Login or Register  to view this content.

[sintek]

....Delete.....

[sintek]

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?

Please Login or Register  to view this content.

Perhaps you should upload a sample

[drjarmin]

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

Please Login or Register  to view this content.

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)...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Example.jpg

[drjarmin]

*Deleted* Managed to combine image with post above.

[sintek]

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

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

Please Login or Register  to view this content.

[drjarmin]

Here is an example

1. 'Run Example Macro in this Workbook.xlsm'

This contains a module with one macro called 'Example'

2. 'ImportData.xlsx'

This is the data to import. Path for this file is hard coded as 'C:\temp\' in the macro above

[drjarmin]

Aiya, very easy when you see the solution! Thank you, perfect.

.ActiveSheet.Cells(1).CurrentRegion & .SpecialCells(12).Copy and its all working.

Really appreciate the help!

[sintek]

Here you go...

Please Login or Register  to view this content.

[drjarmin]

For my understanding, why doesn't 'selection.copy' work? That is the code the macro recorder generates.

[sintek]

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[drjarmin]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[sintek]

Your code does work...

Please Login or Register  to view this content.

Try this

Please Login or Register  to view this content.

[drjarmin]

Yes that works! Thanks again.

Now, the problem is the data is numbers being stored as text so when I sort them I get odd results (and those little green triangle error boxes on the top left corner). Going to try figure that out or start a new thread.

[sintek]

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