Converting Formulas using a template .xlt 1

[jazminecat23]

Hi again folks - you know, with all my posting this week I'm going to have to bring the snacks for the rest of the month. I've got most of the bugs worked out of my code, thanks to your help. But now I'm stymied.

I have a function in an excel spreadsheet that loops through a set of values on sheet 1, plugs them into sheet 2, performs calculations, and then saves the workbook, and then goes to the next set of values, lather, rinse, repeat.

I have created a template in which I have all the code below in the onclick event of a button. What I want to happen, is the user clicks the button, and the code runs, and in step 6 I want it to convert all the formulas to values and save the spreadsheet, and then loop to the next one.

Unfortunately, what happens is it converts all the formulas to values on the first iteration, and then on the next run through the loop, the formulas aren't there - they've been converted. (cue Beatles music here)

My code is heavily commented from the testing I was doing. the problem child is in red:

Private Sub CmdRunSpreadsheet_Click()
[green]
'
' To insert values and create monthly budget distribution automatically

'Check for login to spreadsheet server[/green]
    If MsgBox("Are you logged in to Spreadsheet Server?", vbYesNo, "Login") = vbNo Then
    Exit Sub
    Else
[green]    
'1. Get values from Master Sheet[/green]
    Dim r As Range
    For Each r In Sheets("DistList").Range(Sheets("DistList").[A1], Sheets("DistList").[A1].End(xlDown))
      sDept = r.Value
      nVal1 = r.Offset(0, 1).Value
      nVal2 = r.Offset(0, 2).Value
[green]
    'BudgetStatus is where the processing occurs[/green]
      With Sheets("BudgetStatus")
          sName = UCase(Format(DateSerial(.[G2], .[G4], 1), "mmmyy") & Left(sDept, 3)) & nVal2
         .Cells(6, "G").Value = nVal1
         .Cells(7, "G").Value = nVal2
      End With

[green]'2. Unhide button, and all worksheets and rows[/green]

    cmdRunSpreadsheet.Visible = True
    Sheets("GXE Source").Visible = True
    Sheets("DistList").Visible = True
    
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = False
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = False
   
[green] '3. calculate[/green]
    Application.Calculate
    
 [green]'4. Generate Detail Reports, and hide GXE sheet[/green]
    Application.Run ("ExpandDetailReports")
 
[green]'5. Hide button, worksheets, and all extraneous rows on main worksheet[/green]
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True
  
    cmdRunSpreadsheet.Visible = False
    Sheets("GXE Source").Visible = False
    Sheets("DistList").Visible = False
    
[green]'**Works correctly to here 3/22/07[/green]
    
[green]
'6. overwrite formula with values,
'this also stops it from working - the main sheet doesn't even calculate correctly, even though
'the value is correctly inserted into G6 and G7
[/green]
[red]
    Cells.Select
    Range("A38").Activate
    Selection.Copy
     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
          False, Transpose:=False
    ActiveWindow.ScrollRow = 1
[/red]

[green]        
'7. Save the file and loop to the next set of values
[/green]
    SaveAs "C:\Documents and Settings\Administrator\Desktop" & "\" & sName & ".xls"

Next

End If

End Sub

I've made this a template (.xlt) file. I've tried it both directly in the .xlt and by selecting file-->new and using the template as a template. In both cases, I get the same result. I need to convert these formulae to values because they are specific to the Spreadsheet Server program that generates this data from our AS400. if I email a spreadsheet to a user who does not have Spreadsheet Server installed, the values do not show up correctly. Anyone have any thoughts? The formulas are in each cell of G18:K38.

Thank you all!

 

[macropod]

Hi jazminecat23,

Are you sure your code is determining the correct worksheet to use when converting the formula to a value?

A few suggestions to make the code more efficient-

Change:

If MsgBox("Are you logged in to Spreadsheet Server?", vbYesNo, "Login") = vbNo Then
Exit Sub
Else

and:

End If

to:

If MsgBox("Are you logged in to Spreadsheet Server?", vbYesNo, "Login") = vbNo Then Exit Sub

Is it really necessary to unhide then hide all worksheets and rows? In most cases, visibility won't affect how the code runs. I do accept, though, that visibility might affect whatver your users are supposed to be doing at this stage. If it isn't necessary, you could delete those blocks. If it is necessary, change:

Rows("1:8").Select
Range("A8").Activate
Selection.EntireRow.Hidden = False
Columns("A:E").Select
Range("A9").Activate
Selection.EntireColumn.Hidden = False

to:

Rows("1:8").EntireRow.Hidden = False
Columns("A:E").EntireColumn.Hidden = False

and change:

Rows("1:8").Select
Range("A8").Activate
Selection.EntireRow.Hidden = True
Columns("A:E").Select
Range("A9").Activate
Selection.EntireColumn.Hidden = True

to:

Rows("1:8").EntireRow.Hidden = True
Columns("A:E").EntireColumn.Hidden = True

Subject to my previous comments about which sheet you're actinfg on, you could also change:

Cells.Select
Range("A38").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
     False, Transpose:=False
ActiveWindow.ScrollRow = 1

to:

Range("A38").Value = Range("A38").Value

Cheers


[MS MVP - Word]

 

[SkipVought]

Hi,

I can't tell which sheet is which where you do not have a sheet reference.

    Cells.Select
    Range("A38").Activate
    Selection.Copy
     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
          False, Transpose:=False

You might want to do something like this...

    with [i]WhateverSheetObject[/i].Cells
       .Copy
       .PasteSpecial _
          Paste:=xlValues, _
          Operation:=xlNone, _
          SkipBlanks:=False, _
          Transpose:=False
    end with

Also, why do the unhide and hide statements? I recommend avoid using the Activate and Select methods in most cases, except to display the sheet/range you want the user to see when the procedure ends.

Skip,

_[red][/red]

 

[jazminecat23]

Good morning! Thanks for your replies. I'll try to answer them top down.

Macropod -

I am sure it's converting the right sheet's formulas to values, because it doesn't touch the formulas in the other sheets (yet). Eventually, I need it to convert them on three sheets. Right now, I'm just trying to get it to work on the one.

I put the hide and unhide in there because it didn't seem to be running the detail reports if the GXE sheet was hidden. Probably something particular to Spreadsheet Server. Once I figure out how to make the values paste correctly, I will definitely go through and remove any repetitive hide and unhide, one line at a time. The hides need to stay in at the end, so that the sheet that we email out at the end looks cleaner and isn't confusing to the recipient. And we can't change the layout of the sheets - that too is specific to spreadsheet server. I will definitely clean them up - the code for that came from recording a macro. Thanks for the info - I wasn't sure what was necessary and what could go.

Skip - I tried both of the following and get a runtime error. This was just code I recorded using a macro, so obviously it needs to be cleaned up, but I"m missing something:

  With Sheets("Budget Status").Cells(38, "A")
   .Copy
       .PasteSpecial _
          Paste:=xlValues, _
          Operation:=xlNone, _
          SkipBlanks:=False, _
          Transpose:=False
    End With

and

  With Sheets("Budget Status").Range("A38")
   .Copy
       .PasteSpecial _
          Paste:=xlValues, _
          Operation:=xlNone, _
          SkipBlanks:=False, _
          Transpose:=False
    End With

Still the main problem exists that once it iterates through the first item on the DistList sheet, calculates all the sheets, overwrites the formulas on the Budget Status sheet, and saves the workbook, when it goes to the next iteration, it can't calculate anything, because the formulas aren't there anymore - they were pasted over with values...

I thought that making it a template would prevent that happening. I need the formulas there to make it calculate every sheet correctly, and then save off that sheet and move to the next set of values, calculate, save, etc, ad infinitum. (well, about 50 sets, actually). I need the formulas to not be in the final saved off sheets because they are proprietary to Spreadsheet Server - if someone opens a spreadsheet created with Spreadsheet server, and they don't have it installed, it will just show #VALUE# in all the formula cells.

So my dilemma is - how to convert the formulas to values on each workbook, after calculating it, but then return to the template with the formulas and iterate through the next item on the dist list and repeat the process? I do notice that once it saves the first iteration, the filename of the saved workbook is what shows in the toolbar, and it appears that the template itself is closed. This perhaps is a clue to the problem?

Thank so much for your help! I will get to cleaning up my code now.

 

[SkipVought]

"...overwrites the formulas on the Budget Status sheet..."

Of course it does. That's what you are asking it to do.

FIRST copy the SHEET TAB to a new workbook. THEN copy/paste special.

Skip,

_[red][/red]

 

[jazminecat23]

Hi Skip - Thanks so much for helping me. How can I convert the values on the copied workbook, do the file-->save as, and then return to the main sheet to create the next one? I thought that by using file-->save as, I was creating a new workbook. Obviously I'm going about this all wrong. So, after I run the calculations, I select the 5 worksheets I want to save off, copy them, and paste them into a new workbook.

I used this to copy the necessary sheets to a new workbook:

  Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
        "Detailed Report", "JE Detail")).Select
    Sheets("Detailed Report").Activate
    Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
        "Detailed Report", "JE Detail")).Copy

When I run it that way, creates the new workbook fine, but then it errors out on the part to select the cells to copy. If I record a macro on the new sheet, and then paste that into my original code on the template, I get this, which also errors out with "runtime error 1004 - select method of range class failed."

Range("G18:K38").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

So I guess the dilemma is how to run code from this main workbook that effects the new workbook, save the new workbook with the save as function in this code, close the new workbook, and then run the code again with the next set of values? Is that possible??

Brain hurts, but I'm learning a lot this week from you. Thanks a million.

 

[SkipVought]

When you COPY the sheets to a new workbook, Set a Workbook Object of that workbook

  Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
        "Detailed Report", "JE Detail")).Copy
Set wbNew = ActiveWorkbook
'note that wsNew.Sheets("BudgetStatus") is a different sheet than ThisWorkbook.Sheets("BudgetStatus")

'now replace formulas with values
for each ws in wbNew.Worksheets
  ws.cells.copy
  ws.cells.pastespecial xlpastevalues
next

Skip,

_[red][/red]

 

[jazminecat23]

Hm okay cool! So then, I can do the saveas on the wbNew right? Can I then close the wbNew with something like wbNew.Close and return to the first workbook and repeat the process? Do I have to then use a Set wbTemplate = ActiveWorkbook statement to get back to that?

It seems like it's starting to make sense. thank you!

 

[SkipVought]

No, wbTemplate is that workbook until you wbTemplate.close and Set wbTemplate = Nothing.

Skip,

_[red][/red]

 

[jazminecat23]

hm, i'm definitely confused, but infinitely grateful for your patience here. So, to save the new workbook after overwriting the formulas, and then close that new workbook. What I have so far is:

wbNew.SaveAs "C:\Documents and Settings\Administrator\Desktop" & "\" & sName & ".xls"

then I put in

sName.Close

to try to close that new workbook, but I get

Runtime error 424, Object Required.

I'm assuming if I can close the new workbook, it will then make the template, called BSDist.xlt, the active workbook and start the process again?

 

[PHV]

Replace this:
sName.Close
with this:
wbNew.Close

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

sName is a STRING, not an OBJECT.

You could do...

Workbooks(sName).Close

Skip,

_[red][/red]

 

[jazminecat23]

Hurray!! That was the last piece of the puzzle! It works, perfectly, and my template is left intact at the end. Thank you SOOO much for walking me through this this week! You are my new hero. I can't possibly thank you enough.

 

[jazminecat23]

I spoke too soon. Scope creep is not my friend.

Now the boss wants to a whole slew of hide columns on the new workbook. I can't hide them on the original, because the contents of this sheet are actually generated by Spreadsheet Server. I have this but it gives a runtime error 9, subscript out of range.

   Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
    "Detailed Report", "JE Detail")).Copy
    Set wbNew = ActiveWorkbook
    Sheets("JE Detail").Activate
[red]
    Columns("D:F").Select
    Range("D2").Activate
    Selection.EntireColumn.Hidden = True
[/red]

any thoughts for this one last bit?

 

[SkipVought]

    Set wbNew = ActiveWorkbook
    with wbNew 
        .Sheets("JE Detail").Columns("D:F").EntireColumn.Hidden = True




    end with

Skip,

_[red][/red]

 

[jazminecat23]

<napoleon dynamite>
gaaaah!
</napoleon dynamite>

I tried everything *except* that! OY! thanks again Skip. Wish I could give you more than stars this week!

 

[SkipVought]

Just remember, you have sheet name ambiguity with the copy. You have to reference to the WORKBOOK to define a CLEAR unambiguous ogject.

Skip,

_[red][/red]

 

[SkipVought]

<napoleon dynamite>
You have a large bowl of vanilla, chocolate & strawberry ice cream.

You light a 6" firecracker and burry it deep into the ice cream.

The firecracker explodes, spreading the ice cream in all directioons.

What do you have?

[highlight white][white]Neopolitan blownapart[/white][/highlight] ;-)
</napoleon dynamite>


Skip,

_[red][/red]

 

[jazminecat23]

Neapolitan Dynamite!

...wow, I really miss ice cream.

 

[SkipVought]

You don't have ice cream in the great northwest?

Skip,

_[red][/red]