Excel Challenge

[jwindholz2725]

I'm sure it is possible but have no idea where to begin. Trying to make a long story short, we will pull a sheet into our workbook and then create tabs based on the data in the workbook. The format is consistant. I would like to read from the sheet we import. For example I would like to take the names from say A1:A40 from the imported sheet (for reference the tab is named WBS) and have excel create tabs off those names. So if the names in A1:A40 (of the WBS tab) are 1,2,3...40. Excel would create tabs named 1,2,3..40.

So I know the code would need to take data A1:A40 in the WBS tab, and then create a tab for cell A1, A2...A40.

Is this possible??

 

[Trevor G]

Try something like this. A pointer you can't have the same information in a cell that is within the range. No dublicates

Sub AddSheets()
'Create new sheets based on range
'name each sheet from that range
Dim c As Range
For Each c In Range("a2:a20")
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c

Next c
End Sub

 

[jwindholz2725]

Wow, I guess that wasn't much of a challenge. LOL. You did it too quick, that worked perfect.

Thank You!!!

 

[Trevor G]

Your welcome

Thanks for the feed back

 

[jwindholz2725]

I had one more question. While this code works great, it creates a new sheet. Is there a way to have it copy that tab based of the names in cells A1:A40 rather then create a new sheet?

 

[Trevor G]

This code will look to copy your worksheet

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> ws = ThisWorkbook.Worksheets("WBS")<br>    Application.Calculation = xlCalculationManual<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 20<br>        Debug.Print i<br>        ws.Copy Sheet1<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>     <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jwindholz2725]

You are a genius sir. That worked perfect….Thank You!!!!!!!!!!!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Trevor G]

Pleased to read it helped you find a solution.

 

[JustAnOilMan]

I can see that this thread is from last year, but after having searched the threads for a while looking for help I believe this one is closest to my problem.

I am wanting to import sheet 1 & 3 from a workbook, which will have a different name every time, and place them as sheet 1 & 2 in my workbook. The 2 tabs are called..."Tracing Page (Data)" and "SpectraChem".
Any help is much appreciated, learning as I go.
Using Office 2010 btw.

 

[jproffer]

You mean the 1st and 3rd sheets from the left every time, regardless of name? And insert them into a new workbook as the 1st and 2nd sheets, respectively...correct?

What are the 2 workbook names? From "This workbook" and to "That workbook"

By the way, it's probably better to start a new thread. Many of the members here key mostly on the threads with zero replies (including myself...usually). The only 2 that will probably pay attention to this thread are the OP and Trevor G....and apparently me .

They don't expect you to find a similar thread for every question you have. Although, they do somewhat expect you to make a mild effort to search for something similar and THEN if that search doesn't give you a solution that works for you, it truly is better to start a new thread.

If you haven't had an answer within a day, go back to the original thread and "bump" it back to the top of page 1. I'd caution against doing this too soon though (at least several hours, if not a full day) because then it won't have zero replies and, as I mentioned above, will be less likely to be noticed.

*****DEEEEEEEP BREATH****** now with all that said

Welcome to the board.