find file in directory, copy and paste worksheet

[inky]

i would like to create a macro to allow users to find the appropriate file in the directory, copy the worksheet and paste it into the worksheet where the macro was clicked.

the file users select will only ever contain one worksheet.

i found the attached macro yet as it lists all files in the directory, i would like to modify it as follows:
1. only show .xls files.
2. copy the found file worksheet and paste it into the worksheet.

thanks in advance

[Leith Ross]

Hello Inky,

Change the worksheet name from "Sheet1" in the code to the name you will be using.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[inky]

hello Leigh

thanks for that - works great!

is it possible to amend the macro as follows: rather than have the opened EXCEL file added as another sheet to the open workbook, the data is transfered to the sheet from which the macro is run via a "copy and paste"?

for example, user clicks button on 'sheet1' and the appropriate data is copied into sheet 1, user clicks button on 'sheet2' and the appropriate data is copied into sheet 2 etc.

thanks

inky

[Leith Ross]

Hello inky,

If you want to copy only certain cells, you will have to tell me which ones you want copied and where they will be pasted.

Sincerely,
Leith Ross

[inky]

hello Leith

i would like to copy the entire sheet from the file into the workbook sheet.

yet as the data range differs i think it may be easier to include a "select all" (similar to a user clicking the top left button between the row headings and column headings, copying, and pasting).

cheers

inky

[inky]

i'm hoping someone can help me with the code below.

the code posted by Leigh Ross works great and i have used it as a starting point. (i've commented it out as it's the only part that works - not surprising)

i hope to achieve the following; the found "sheet1" is pasted into the sheet from which the user ran the macro, rather than copied into a new sheet in the workbook. for example, a user run's the macro from sheet 3 in book1, than sheet1 from the found workbook is copied and pasted into sheet3. i used the "select all cells" as the shape/size of data on the found sheet1 will differ.

Please Login or Register  to view this content.