Importing worksheet into my workbook

**tryer** · 10-20-2008, 08:25 AM

I am using the (borrowed) code below to select and import a worksheet into my workbook.

Please Login or Register  to view this content.

Unfortunately it is failing in two places

Please Login or Register  to view this content.

I assume this is just colouring a tab so I have deleted this line but I can't get over this line

Please Login or Register  to view this content.

I think is is due maybe to conflicting worksheet names. Ideally I would like it to import all the data on the source worksheet into my own workbook sheet called say "Data". I think that maybe this isn't the right code as it is importing the worksheet and not just the contents of it. However that shouldn't be a problem as I can write code to copy the data from this imported sheet into the one I want then delete the redundant sheet but I still need to overcome the error on import.
Any Idea's?

**royUK** · 10-20-2008, 08:28 AM

Are you using a ListBox?

**tryer** · 10-20-2008, 08:50 AM

Roy,

I have copied the code from the attached sheet which was posted on here some time ago. I found it does what I need in that it asks the user to browse for and select the source data.

**royUK** · 10-20-2008, 09:44 AM

Change the import button code to this

Please Login or Register  to view this content.

**tryer** · 10-20-2008, 09:59 AM

Thanks Roy, That works exactly as I wanted it to.

You wouldn't believe how long I have been messing around with that code trying to make it work.
Now trying to understand what you have done, In your line

Please Login or Register  to view this content.

I assume .usedrange.copy copies all the used cells from the the source worksheet, ThisWorkbook.Worksheets("Data").Cells selects the destination but I dont see any paste instruction? so then does putting the destination directly after the copy command cause the paste?

sorry to be a pain but I'm trying to learn rather that just ask you guys to do my work for me

**royUK** · 10-20-2008, 10:17 AM

Just a tip, if you highlight words like UsedRange in the VB Editor & press F! you will get hel on it.

UsedRange is exactly what you think, the range that has been used on the sheet. The way that I wrote the copy specifies the destination.

**tryer** · 10-20-2008, 10:31 AM

Thank you

**royUK** · 10-20-2008, 10:40 AM

No problem, can you mark the thread solved

**tax lady** · 10-20-2008, 06:07 PM

Hi Roy. I have been searching threads, because I am trying to figure out a way to import a worksheet from one workbook to another. I love the file that Tryer attached in one of the replies in this thread. I need something very similar to this, only I need to be able to specify both the worksheet to be copied AND the workbook to copy it to. Can you help?

thanks
marcia