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??

 

[JustAnOilMan]

OK, I will definitely do that in the future, thanks for setting me straight.

Yes, the 1st and 3rd sheets of workbook 2 is what I want to import, and they will always be sheets 1 & 2 in the workbook I want to import them to.
How I really want it set up is as follows.

Open workbook 1 (the one I want to import TO) and have a macro button on page 1 of that workbook that would ask where to find the workbook (2) I want to import FROM. Then auto import sheets 1 & 3 from the chosen file as sheets 1 & 2 in my workbook.
'
The name of workbook 1 will be 'Schem Timesaver' (with a revision suffix xxx) and the name of workbook 2 can vary but will always be chosen by whomever is using the file.

 

[jproffer]

This is going to be a work in progress, but try this out on copies of everything involved. Don't use the original workbooks just in case.

See comments and report back.

Sub test()
Dim MyPath As String
MyPath = InputBox("Please specify the path of the workbook to import")
Workbooks.Open (MyPath) ' this may have to be changed, depending on how much of
                        ' the path you have to put into the inputbox
'this should make the copied worksheets the first and second sheets
ActiveWorkbook.Sheets(Array(1, 2)).Copy Destination:=Workbooks("Schem Timesaver")

End Sub

 

[jproffer]

CORRECTION: Don't use the one in the previous post.

Sub test()
Dim MyPath As String
MyPath = InputBox("Please specify the path of the workbook to import")
Workbooks.Open (MyPath) ' this may have to be changed, depending on how much of
                        ' the path you have to put into the inputbox
'this should make the copied worksheets the first and second sheets
ActiveWorkbook.Sheets(Array(1, 2)).Copy Before:=Workbooks("Schem Timesaver.xlsm").Sheets(1)
 
End Sub

 

[JustAnOilMan]

OK....I hafta learn how to speak VBA....that was almost perfect right off the bat....just had to change the (Array 1,2) to (Array 1,3) to get the correct sheets.
However....this file is going to be used by people much less computer literate than myself, so it would be nice to have a browse button on the dialog box that specifies the path instead of having to type it in, not a problem for me but i'm sure it would prompt a phone call if left to them to figure it out.
The path will be different by user depending where they store the files on their respective machines.

 

[jproffer]

How about popping up the "open" dialog?

I left the other stuff in for now, commented out, in case we go back to that later. Try this out:

Sub test()
Dim MyPath As String
Application.Dialogs(xlDialogOpen).Show
'MyPath = InputBox("Please specify the path of the workbook to import")
'Workbooks.Open (MyPath) ' this may have to be changed, depending on how much of
                        ' the path you have to put into the inputbox
'this should make the copied worksheets the first and second sheets
ActiveWorkbook.Sheets(Array(1, 3)).Copy Before:=Workbooks("Book1.xlsm").Sheets(1)

End Sub

 

[JustAnOilMan]

Hmmmmm.....It gets as far as allowing me to chose the path and file and it opens the other workbook, but then pops up a Subscript Out Of Range error....

 

[jproffer]

I keep forgetting to change the destination book name. Sorry about that...my error, not yours.

Sub test()
Dim MyPath As String
    Application.Dialogs(xlDialogOpen).Show
'MyPath = InputBox("Please specify the path of the workbook to import")
'Workbooks.Open (MyPath) ' this may have to be changed, depending on how much of
' the path you have to put into the inputbox
'this should make the copied worksheets the first and second sheets
    ActiveWorkbook.Sheets(Array(1, 3)).Copy Before:=Workbooks("Schem Timesaver.xlsm").Sheets(1)
 
End Sub

 

[JustAnOilMan]

The only thing left to do is Buy You a Beer!
I can integrate all the data from the imported pages myself and because of your help will be a hero to many folks that we just made life easier for.

Think I will take some excel courses during our slow time of the year and hopefully be able to understand more VBA myself....Can't write it myself...YET!... but am slowly learning how it is used and the fantastic things you can do with it.

I sure am happy I found this forum and the great folks like yourself that give freely of their time to help others...ray:

 

[jproffer]

Not a problem.

Don't say anything about this forum at work....just let 'em think you've figured it all out overnight.

Courses are fine....trial and error works too. Think of something you'd like to do with VBA....anything. Then set your mind to figure out how to do it.

Use the recorder for a line or two if you get stuck. But don't blindly use recorded macros. The recorder will record everything you do.....EVERYTHING. When you scroll up and down. Selecting cells (rarely necessary). Activating worksheets (again, rarely necessary).

If you see something like:

Sheets("Name").Activate
Range("A1").Select
Selection.Copy
Sheets("Name2").Activate
Range("A1").Select
ActiveSheet.PasteSpecial xlPasteAll

That can all be combined into:

Sheets("Name").Range("A1").Copy Destination:= Sheets("Name2").Range("A1")

Get in the habit early on of dimensioning your variables.

Dim MyString as String
Dim MyNumber as Long
Dim MyDecimal as Double

some people put the type at the beginning of the variable name like

Dim strMyString as String
Dim lngMyNumber as Long
Dim dblMyDecimal as Double

I don't, but to each their own.

Avoid using variable names like Cell, Range, Selection, Row, Column...it gets confusing in a hurry


Anyhow, that's the end of my rant. Other than that, just trial and error .

Good luck.

 

[JustAnOilMan]

Yes....I'm of the school of thought...."Think it and make it happen".

I am at heart a lazy bugger so all the stuff I do....'for others' ...is ultimately going to make my life easier...

Once again...thanks a BUNCH! and I won't tell if you don't tell...