Import data from selected excel file into a specified worksheet

Hi All,

I have a workbook which, amongst several worksheets, also includes 12 blank worksheets (one for each month of the year).

Is it possible to have a macro that will (a) first prompt the user to select an excel file to import (the excel files to import are provided each month and only have one tab); then (b) select which worksheet to import the data (values only) from the selected file into.

My experience with VB is basic at best so any help is appreciated...

Hi

I'm assuming that the file you want to import has some reference to the Month in question? This is a pre-requisite in order to finding to which sheet the import data should be copied. Otherwise you'll need to ask the user to input the month when they load the file and apply that to a variable.

First declare a couple of variables to identify your summary workbook and the workbook to be opened.

e.g.

Dim wbMyBook as Workbook, wbTemp as Workbook
Then use

Please Login or Register  to view this content.

and later, immediately after the Application.GetOpenFileName

Please Login or Register  to view this content.

To prompt the user to open a file just use the

Please Login or Register  to view this content.

instruction.

Now you can use code like

Please Login or Register  to view this content.

Finish off with

Please Login or Register  to view this content.

HTH

Hi,

I'm attaching an example workbook which prompts you to select a sheet and then asks you to open a file. The macro will copy A1:Z10 from the opened file into the sheet you selected.

You'll need to adjust the A1:Z10 range as necessary.

Either click the blue oval on the Jul08 sheet or Ctrl-m for the menu

HTH

Hi,

Thanks for the feedback, and glad to be able to help.
When you have a moment or two would you go back to the original post and mark it SOLVED.

Kind regards,

Hello Scott,

Is the data you want to copy in contiguous rows? i.e. where no row is completely empty? If so you can use the
.CurrentRegionProperty of the range. So instead of Range("A1:Z10") in the code below, use Range("A1:Z1").CurrentRegion.Copy

However if the first row of the range to be copied varies the macro will need to be able to determine the first row. Probably with a .Find instruction.

The file I gave you does record the sheet to which the copy should be made - unfortunately I set the sheet name variable after the other workbook is opened and not before. The code below corrects that.

Regards

Please Login or Register  to view this content.

Originally Posted by navyav8er

Richard,
Yes, it is contiguous rows, so your recommendation worked. I was intrigued by the "PasteSpecial" line in your code. I was wondering if that would allow the formatting (boarders, column width) to be copied as well and how you would use it?

As for the second part of my previous post, I don't think I was clear enough as to my problem. Is there a way to choose which worksheet to copy from? e.g. I have multiple worksheets in "stFName" it only copies from the last worksheet that is visible when the workbook opens. How would I add the ability to choose which worksheet to copy from? Thanks.

Scott

Hello Scott,

Subsequent to a Copy instruction you can choose to PasteSpecial() in several ways
i.e.
xlPasteAll
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats


Re your second Q. Do you know in advance the name of the sheet? If so you could use the
Sheets("IKnowTheSheetName").Activate
instruction to select it.

If you don't know then you'd need some additional VBA code to read all the sheet names and populate a userform listbox with them so that you could then pick one.

Regards
i