File dialog list picker

**julhs** · 02-27-2013, 11:23 AM

What I would like is some advice and or suggestions as to how to deal with my next step. Ultimately what I am trying to do is; from a CommandButton select a Workbook from the list, then select a Sheet from a list of Sheets in that book, then copy and paste into that sheet.

I have put together the code below which gets me to the point of selecting the Workbook but stuck on how to then list the sheets in it before continuing.

Please Login or Register  to view this content.

My thoughts are, is a modification of this be feasible?

Please Login or Register  to view this content.

**Norie** · 02-27-2013, 11:46 AM

You could create a small userform with a listbox that lists the worksheets in the workbook.

Perhaps something like the attached.

**julhs** · 02-28-2013, 11:51 AM

Thank you Norie for your response.

Only had time to have a quick look at your suggestion, it could be my only option. All I do know is that I have hit a brick wall with the route I was going.
I still need to work out the code for the copy and pastespecial.

This is what I have from a recorded macro that I am looking to adapt.

Please Login or Register  to view this content.

Any help you can offer is gratefully received

julhs

**Norie** · 02-28-2013, 12:03 PM

julhs

In that code which workbook is the one you have opened?

Also, there are no worksheet references in it so where are you copying and pasting from/to?

**julhs** · 03-01-2013, 09:36 AM

Norie

In brief, we are copying a range of cells FROM the Workbook sheet that we are ON and pasting values&comments TO the Workbook sheet selected through the filter process.

Detail is: Starting from UserForm1 on a sheet in "*TestingBook.xlsm" (all sheets in the book have identical layout, maybe 20 -50 of them) copying
range A11:B25 FROM the sheet we are on; go through process of identifying the correct Workbook (“*Invoice.xlsm”) and then the correct Sheet
within it (identified in the ListBox) then paste values&comments in the range starting at cell A19 on that Sheet.

If the destination worksheet remains hidden then want a final safety check Warning message pop up ‘Do you want to overwrite data’ IF the range A19 – A33
already contains data (could be text or numbers)

I then need to repeat the copy & paste values&comments process FROM and TO the same Workksheets but for other ranges:

Y11:Y25 to D19
A34:B48 to A36
Y34:Y48 to E19

Warning message not required after the first one that establishes Sheet is safe to copy too. If can’t skip them then, best thing would be to have the
destination Worksheet on view BEFORE starting to paste into it so can SEE sheet does not contain data.

Hope that is not overkill on the detail!!

Many thanks
julhs

**Norie** · 03-01-2013, 10:18 AM

julhs

You mention worksheets but in the original post you appeared to only want to be able to select one worksheet.

**julhs** · 03-01-2013, 12:42 PM

Norie

That was a little misleading, will only EVER be copying from one Sheet in “*Testing Book.xlsm” and pasting to one sheet in “*Invoice Book.xlsm” at a time.
(“*Testing Book.xlms” contains monthly sheets with job details ”*Invoice Book.xlms” is just that, monthly Invoices).

It is just that I have a series of these two Workbooks for each customer and the process of copying from one and pasting into the other is the same at month end for all of them.
So this whole process is about producing a generic code that will work across all of the pairs, month after month without having to constantly change references.

The constants are:
a) All “Testing books” are identical in format to each other, similarly ALL “Invoices Books” are identical to each other.
b) Start point is always by opening ONE “Testing Book."
c) There is always a pair of these Workbooks for each customer.

The variables are: All pairs of Workbooks will have matching prefix ie “aaaTesting Book.xlsm, aaaInvoiceBook.xlsm” but the next pair will be “abcd” & so on.

Is that any help

julhs