How can I update my marco to export my output to a new workbook with hard coded values

[roledzki]

I am trying to get my macro I have been working on with some peoples help here to export the templates i am exporting to a new workbook, how would I go about modifying the code to do this? I have attached the workbook and the code below

Sub AddTemplateSheet()
Dim myCell As Range, myRange As Range

Set myRange = Sheets("Input").Range("F8")
Set myRange = Range(myRange, myRange.End(xlDown))

For Each myCell In myRange
If myCell <> "" Then
If Not Evaluate("=ISREF('" & myCell.Value & "'!A1)") Then
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = myCell.Value
End If
End If
Next myCell
End Sub


https://www.dropbox.com/s/1y311hbbg2...orum.xlsm?dl=0

Thanks for your help!

[ImranBhatti]

Try this

Please Login or Register  to view this content.

[/CODE]

[Keebellah]

I have looked at your file and am reading what you ask but I honestly do not understand what you want to do.
You select all the cells in column F form row 8 to 111
Then you query each cell in that column and if it references correctly yo add a new worksheet at the end with the value in that cell.
What are you exporting to a new workbook?
If you could please explain it i a way that we can understand you we will be able to help you.
I am sure that for a person who knows what he is doing it is quite clear but you need to explain it to someone that has absolutely no idea (yet) of what you really require.
The cells in the Input worksheet in column F8-F111 return empty values so I cannot even test it

[spitfireblue]

Maybe...

Please Login or Register  to view this content.

[roledzki]

So the need has slightly changed, in the template sheet I have a bunch of formulas that pull data from a hyperion server and I have to update all of the pages before I can export to a new workbook. The new workbook would also need the numbers to be pasted as values.

So I am guessing I would change the above formula to Sheets("Template").copyafter and then I am not sure how you would go about pasting these sheets as values.

[Keebellah]

Since I have absolutely no idea what the import looks like and how the 'final "Template" sheet will be filled I cannot help you.

If you want to save a worksheet as a new workbook

Please Login or Register  to view this content.

You can always select all cells and paste them again as only values to get rid of the formulas

Please Login or Register  to view this content.

[roledzki]

Hi Keebellah,

Let me explain what I am doing in this workbook to see if it helps you. This is just an example with any confidential data removed. For the actual worksheet the template is a financial data template that has formulas to pull data from a hyperion server. In the input sheet you have a drop down list of departments and when you select one department a list of all the sub departments is produced. When you run the macro it produces a sheet for every sub department with the sub department number in that sheet. These sheets then pull the hyperion data for that sub department. The last step i am trying to figure out is a second macro that will export the new created sheets into a new workbook as values. I hope this clears things up, let me know if you need more detail.

Thanks,
Rob

[Keebellah]

Hi Rob,
I don't see a link to any file but anyway your explanation tells me more now.
One you have the sheet you want you can use the macro I showed to save it as new workbook.
It copies it and saves it as new workbook.
Instead of the "Template" you pass it the name of the new wroksheet.
Create a Sub with a aprameter to do so

e.g

Please Login or Register  to view this content.

Something like this, you will have to check Syntax and so since I just copied and typed, not tested for errors.