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!

 

[jazminecat23]

Oh, we have lots of it up here! Not as much as in the midwest but... unfortunately, I'm not allowed dairy anymore. Or wheat. tricky! Good thing I can cook! But the Ben and Jerry's version of that Neopolitan Dynamite sounds really really good....

 

[SkipVought]

On a related subject (or not) did you read that Buckwheat, from the Little Rascals, became a Muslim and changed his name as many do, like Lou Alcindor became Kareem Abdul Jabar?

Yea, he changed his name to Kareem of Wheat.

See, ther IS a trail! ;-)

Skip,

_[red][/red]

 

[jazminecat23]

audible groan
<ducking>
keep the geeky job
;-)