Referencing Worksheets through a list

[freybe06]

Hello Everyone,

I have some code where I open a new worksheet and make a list of all the worksheets in the open workbook. My goal is to then run through that list and open a new workbook for each worksheet. My code will open new workbooks with the name of each cell in the list as the worksheet name, but the worksheets are blank. I would like the original worksheet to be in the new workbook.

Here is my code:

[VBA]
Worksheets.Add().Name = "WorksheetList"
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
Sheets("WorksheetList").Select

Dim sRange As Range, sCell As Range
Dim WorksheetList As String
Set sRange = Sheets("WorksheetList").Range("A1", Range("A100").End(xlUp))
For Each sCell In sRange
WorksheetList = sCell
Workbooks.Add
ActiveSheet.Name = sCell.Value
Next sCell
[/VBA]

If somebody could help me make it so that each cell that populates in the list is directly tied to the original worksheet it is referencing, that would be great!

Please let me know if you need more information.

Thanks!

[SamT]

Straight from the Help on "Hyperlink" "CreateNewDocument Method":

Creates a new document linked to the specified hyperlink.
expression.CreateNewDocument(Filename, EditNow, Overwrite)

expression An expression that returns a Hyperlink object.
Filename Required String. The file name of the specified document.
EditNow Required Boolean. True to have the specified document open immediately in its associated editing environment.. The default value is True.
Overwrite Required Boolean. True to overwrite any existing file of the same name in the same folder. False if any existing file of the same name is preserved and the Filename argument specifies a new file name. The default value is False.

This example creates a new document based on the new hyperlink in the first worksheet and then loads the document into Microsoft Excel for editing. The document is called “Report.xls,” and it overwrites any file of the same name in the \\Server1\Annual folder.
[vba]With Worksheets(1)
Set objHyper = _
.Hyperlinks.Add(Anchor:=.Range("A10"), _
Address:="\\Server1\Annual\Report.xls")

objHyper.CreateNewDocument _
FileName:="\\Server1\Annual\Report.xls", _
EditNow:=True, Overwrite:=True
End With[/vba]

[freybe06]

Thanks Sam, but I dont think this is what I'm looking for. I don't want to create a hyperlink and I don't want to save the files. I would just like for the macro to run through the list and understand that if it says "Sheet1" in cell A1, the macro knows to open Sheet1 in a new workbook. Is that possible?

Thanks again

[SamT]

I would just like for the macro to run through the list and understand that if it says "Sheet1" in cell A1, the macro knows to open Sheet1 in a new workbook. Is that possible?

You do realize that you are going to get a new XL window for each sheet named in column A, and only the last window opened will be visible until you tell XL to use a different window.

The easiest way to get what you want is to "Move or Copy" the sheet to a new book.

[freybe06]

Sam - I do realize that. I want the user to have to go in to each workbook and save them manually after the macro is done running.

Is it possible to a "Move or Copy" for a dynamic amount of worksheets? If that is possible, would you be able to help me figure out code to have the macro loop through the worksheets and open a new workbook (not save it, just open) for each worksheet except the first and last?

Thanks

[Aflatoon]

[vba]For Each sCell In sRange
thisworkbook.sheets(scell).Copy
Next sCell [/vba]
for example.

[snb]

or

[VBA]
for each sh in sheets
sh.copy
activeworkbook.saveas sh.name
activeworkbook.close
next
[/VBA]

[freybe06]

Aflatoon - I am getting an error that is stopping my macro on that line. This is what the code looked like when I added it in:

[VBA]Dim sRange As Range, sCell As Range
Dim WorksheetList As String
Set sRange
= Sheets("WorksheetList").Range("A1", Range("A100").End(xlUp))
For Each sCell
In sRange
ThisWorkbook.Sheets(sCell).Copy
Next sCell[/VBA]

I also tried changing it to "activeworkbook" instead of "thisworkbook" but I got the same error.

snb - Is that code going to save each new workbook? I'm not actually looking for any saving to be done. I'd just like the macro to open a new workbook for each and leave the workbooks open.

Thanks for the help!

[Aflatoon]

What error? Note that your layout is incorrect:
[vba]For Each sCell In sRange
ActiveWorkbook.Sheets(sCell).Copy
Next sCell [/vba]
is only 3 lines of code.

[freybe06]

Sorry - I dont know why the code copied weird to the post. In the macro, the code looks just like you have it in your post.

Also, just to clarify, the error I'm getting is a "Type Mismatch" error.

Thanks

[SamT]

Tested in Excel XP:
[vba]Option Explicit

Sub NewBookAsCellValue()
Dim sRange As Range, sCell As Range
Dim BkCnt As Long 'Tracks new book's index number.
Set sRange = ThisWorkbook.Sheets("WorksheetList").Range("A1:A2") 'A100.End(xlUp))

''''Loop thru the list
For Each sCell In sRange
''''Count open books
BkCnt = Workbooks.Count 'Could set this outside loop and increment inside
''''Copy desired Sheet And make a new Workbook
ThisWorkbook.Sheets(sCell.Value).Copy
''''Name the new workbook
'Can not name until Saved. SaveAs renames and
'saves the file in the current folder.
Workbooks(BkCnt + 1).SaveAs Filename:=sCell.Value
Next sCell

End Sub
[/vba]

[Aflatoon]

Probably needs to be explicit - I assume there are no error values in the cells in that range?
[vba]For Each sCell In sRange.Cells
Activeworkbook.sheets(scell.value).Copy
Next sCell[/vba]

[SamT]

Oops, the second time around the previously created workbook will be the active book.
[vba]
For Each sCell In sRange.Cells
Activeworkbook.sheets(scell.value).Copy
Next sCell [/vba]

[Aflatoon]

Ah yes - I knew there was a reason I was using ThisWorkbook originally!

[SamT]

Aflatoon, I tried
[vba]
Set NewBook =ThisWorkbook.Sheets(sCell.Value).Copy[/vba]
But kept getting an Object Required Error the second time around. That's why I used the BkCnt in the above example.

Any idea why the error?

[Aflatoon]

Copy doesn't return anything. You have to refer to activeworkbook after a copy to get a reference to that new workbook.

[SamT]

Right.

I knew that.

Just needed somebody to hit me on the head to knock the knowledge into my forebrain.

Thanks.

[freybe06]

That did it! Thanks Aflatoon!

And thank you to everyone else that gave their input as well. I really appreciate you guys taking the time to help out.

[snb]

But what's the merit doing this ?