Solved: Import by tab name

[ndendrinos]

http://vbaexpress.com/forum/showthread.php?t=13714
Very similar to above I need help in Command Button1 of UserForm1
I need the code to import by the specific sheet name entered in the text box.
(as the code stands now it imports just the first sheet of each file stored in folder "Vault")

many thanks

[lucas]

So you want to import a specific sheet(entire sheet) from a closed workbook into your open workbook?

How are you finding this workbook? Is it in the same path as your open workbook? Do you wish to browse to it?......questions

(as the code stands now it imports just the first sheet of each file stored in folder "Vault")

What code are you referring to....no code is posted at the link you give.....

[ndendrinos]

hello Lucas. Not sure why the original (RAR) attachment does not show in my post but I attach here a winzip version of it ... still not working ... give me a few minutes pls. Not sure how to fix this ... I convert the folder that contains 2 files to a zip one. The zip file created looks fine but when I try to attach it I see it as a RAR.
Not sure why I removed RAR from my computer ... I will have to work on this longer

[lucas]

You can post twice if necessary and attach one excel file to each(not zipped)....I have a little time so no worry.

[ndendrinos]

cleaned the registry and had to reboot to get it going but now it works sorry for the delay and thank you

[lucas]

Hi Nick,
Sorry for the delay. I may have gotten off base as I used a combobox instead of a textbox....easy to change if it's a problem. You could also have it easily delete all sheets at the end if desired except the ones you wish to keep. Let me know if this works for you.

[ndendrinos]

Thank you for your reply Lucas.
The combobox is OK too.
Importing all sheets from the workbooks and then deleting in order to keep one date is also OK
My comcern is that based on 200 working days a year and based on many workbooks that's a lot of importing and deleting to do.
In my mind the macro would run , match the date in the text box to the tabs in each workbook within the folder "Vault" and import just these. (If when going through the tabs the desired date does not exist then the code would go on to the next workbook)
I'm sure this is what you have in mind also , so any solution you come up with is welcome.
Another route is to have code in each of the workbooks in "vault" send the data to Access" trouble is I know how to import a unique record from Accesss to Excel but not a group of records with the same date .
I guess I could do what you suggest and import the whole table and then filter it.... bu then again I'm doing the same as importing directly from all the workbooks as in my attachment.
So let us stay with this existing scenario .
Thanks again

Addendum: just noticed you've included and attachment with your last reply & need time to look at it.

[ndendrinos]

Lucas, the sample you provide is perfect and yes I need to delete all the new tabs created.
Need to add code to delete all sheets between Sheet1 and sheet ("master")
Great work with thanks

[ndendrinos]

somthing different that this perhaps:
[vba]Sheets(Array("Jan2", "Jan2 (2)", "Jan2 (3)")).Select
ActiveWindow.SelectedSheets.Delete[/vba]
but rather :
[vba] If sheet NOT Sheet1 OR Master then delete sheets[/vba]

[ndendrinos]

Also needs an "error handler" in case there is no sheet in the work book that match the selection in the combo.
Other than that it run like clockwork

[mdmackillop]

Hi Steve,
Seems to me this will transfer only 3 cells from each sheet.

Also, for other situations, copy and paste or pastespecial will get the formulae and formats.

[vba] 'Data range in worksheet - starts from second row
'as first rows are the header rows in all worksheets
Set rng = sht.Range("A2:C2")

'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
[/vba]

[ndendrinos]

Hello mdmackillop and thank you for your observation. The range A2:C2 is intentional but could I not expand the range down the road?
As to :

Also, for other situations, copy and paste or pastespecial will get the formulae and formats.

Not sure I understand.
The code pastes "values" only or at least I think it does .
In the affirmative this also is desirable.
Please correct me if wrong on either counts.
As always learning from you

[mdmackillop]

There is nothing "wrong" with Steve's code, but some small inconsistencies. These posts may well be viewed by others with out your specific requirements.
If the range to be copied is set, then simplify things using the range dimensions in Resize, rather than calculated values.
The code does indeed copy Values. I was just pointing out that this may not always be desirable.

[lucas]

Hi Steve,
Seems to me this will transfer only 3 cells from each sheet.

Hi Malcolm,
As Nick pointed out...that was all the info he wanted merged to the master sheet...

Will try to help him delete the extra sheets if I have a minute that are left after the merge...

[ndendrinos]

All clear now with thanks
I think you mean to use something like or similar to this expression (R1,3) instead.
I'm still restling with the error handler needed and the deletion of the woksheets created by the first macro.
When I solve it I will mark the post "solved"
Thank you all

[ndendrinos]

Hello Steve and thank you for revisiting.
I have to leave now but will be back hat in hand to check.

[lucas]

This will delete all the sheets except sheet1 and Master when you click the button on the second userform.....didn't get to the error handler yet...

[ndendrinos]

Hello Steve.
Yes that works great in deleting the sheets. Sorry I took time to answer this but I'm busy reading you and Norie at the lounge ...

[lucas]

Rascal...you just had to stir it up...

[daniel_d_n_r]

Sub File_Sheet_Script()

Dim w_s As Worksheet

For Each w_s In ThisWorkbook.Worksheets
s = w_s.Name
UserForm1.ListBox1.AddItem (s)

Next

UserForm1.Show

End Sub

........

might help, then all you do is use the listbox control to use "selected" as
the sheetname to open/delete,,ect

use filesystem object to create a file object if you need to source files other than the one you have open..

make it a global so it works in all workbooks