Copying tabs over (with some complication)

[dc2288]

Hi,

I do this task few times a week and it has been consuming few hours of my time a day on mundane task of copying and pasting tabs over.

Can you guys help me write a macro for the following task?

1) In master version, I have a column (say A1 - A50) where I will input name of files I need. eg (Apple, Orange, Banana ...)

2) I maintain separate files for each of these names on a different folder.
Each file is in C:\workfiles folder. In workfiles folder, I have a folder named after each file and the actual file is named same as the folder. so Apple will be located in "C\workfiles\Apple\Apple.xls" and Orange will be in "C\workfiles\Orange\Orange.xls"

3) Each of the files also have a tab named after itself. So if you open Apple.xls, there will be a tab named "apple"

4) I need to build a macro which looks up the values in input column, and imports the tabs over as new tab in the master workbook. Tabs will keep its original names.

5) Some complications:
a) When I try to use copy tab command (when I am doing this manually) it gives me a whole bunch of popup boxes because the master version has same names defined as the tab I am trying to import in. (i literally have to click through 1000 or so names before I get to import the tab). A solution I found was that I open the file, select entire range (ctrl A) and copy and paste

b) Another complication is that when I (ctrl A) and copy paste it in the master version, it gives me too many custom format error - my master version sometimes goes over 50 tabs and each tabs i am trying to import is formatted using custom formatting. One solution I found was to paste as formula.

Here's what I was thinking to overcome everything
- open the files in input column (apple)
- select accurate tab (apple)
- select all data and copy
- create a new worksheet in master version and name it exactly same (apple)
- paste as formula
- (a huge helper but not critical) there will be a tab called "format". select entire range of format tab, and paste into each created tabs as format only. the format tab will use default excel formatting and not custom number formatting.

Thanks a lot guys and I really appreciate all your help.
I am trying to create this on my own as well, so if you can't help out with the whole macro, any parts that you can contribute to help me build this would be much appreciated!!!



rinse and repeat and go down the input column until it's empty.

 

[dc2288]

I can also create a master version with all the tabs with all the possible names. In that case, we don't need to create tabs and rename them, but rather unhide respective tabs before pasting over. It will also have have been pre-formatted with excel default formatting. So that takes care of formatting issues too.

Then all we would need to do is copy tab and paste as formula in proper tab.

 

[texasalynn]

so here is a snippet of how to create scenarios to open file based off of cell values

Sub openfile()
Dim WB As String
WB = Cells(1, 2) ' this is where the name is found
Workbooks.Open Filename:= _
    "C:\workfiles\" & WB & "\" & WB & ".xls" ' uses variable to fill in value for file
ActiveWorkbook.Worksheets(WB).Activate

End Sub

 

[dc2288]

Awsome! I will use this to open files
Thank you

Anyone else wanna contribute some portion of codes?

 

[texasalynn]

your welcome!

Also just a FYI sometimes when the posts is so long people shy away from answering. You might want to make your future posts be simple, maybe take it one step at a time. Not saying to not provide enough information as to make it difficult to see your request and desired results.