jwise
01-03-2008, 12:16 PM
I wrote macros which essentially copy a workbook with many worksheets (about 40, number varies) to another workbook. The individual worksheets in the new workbook then receive 3 additional columns with formulas in them. I copy these original workbooks using "Cells", and then I copy the formulas using "range copy". When the macros complete, both workbooks are left open and I save them. However, the second set of changes via range copy are not in the new workbook, but the first set of Cell copies are. If I manually do a "SaveAs", then my range copy changes are saved. Any ideas?
Both sets of code work flawlessly in test mode. The only thing that I can see that is unusual is that the last cell that was range copied still has the "moving dashes" around it. If I press "ENTER" in this copied worksheet, the cell becomes normal. Here is the "Cell" code snippet:
Sub CopyRow(wsIn As Worksheet, wsOut As Worksheet, _
rowIn As Integer, rowOut As Integer, number As Integer)
Dim i As Integer
For i = 2 To number
wsOut.Cells(rowOut, i) = wsIn.Cells(rowIn, i)
Next i
End Sub
And here is the Range snippet:
Private Sub RngCopy(ws1 As Worksheet)
Dim rngA As Range
Dim rngB As Range
Dim rString As String
Dim wsName As String
wsName = ws1.Name
Range("P3").Value = "=SUM(C3:M3)*1.09"
rString = "=VLOOKUP(" & """" & wsName & """" & ", APT_nums!$C$1:$D$41, 2, FALSE)"
' =VLOOPUP("BWD", APT_nums!$C$1:$D$41, 2, FALSE)
Range("Q3").Value = rString
Range("R3").Value = "=P3/$Q$3"
ws1.Range("P3").Copy
ws1.Range("P4:P82").PasteSpecial
ws1.Range("R3").Copy
ws1.Range("R4:R82").PasteSpecial
End Sub
Both sets of code work flawlessly in test mode. The only thing that I can see that is unusual is that the last cell that was range copied still has the "moving dashes" around it. If I press "ENTER" in this copied worksheet, the cell becomes normal. Here is the "Cell" code snippet:
Sub CopyRow(wsIn As Worksheet, wsOut As Worksheet, _
rowIn As Integer, rowOut As Integer, number As Integer)
Dim i As Integer
For i = 2 To number
wsOut.Cells(rowOut, i) = wsIn.Cells(rowIn, i)
Next i
End Sub
And here is the Range snippet:
Private Sub RngCopy(ws1 As Worksheet)
Dim rngA As Range
Dim rngB As Range
Dim rString As String
Dim wsName As String
wsName = ws1.Name
Range("P3").Value = "=SUM(C3:M3)*1.09"
rString = "=VLOOKUP(" & """" & wsName & """" & ", APT_nums!$C$1:$D$41, 2, FALSE)"
' =VLOOPUP("BWD", APT_nums!$C$1:$D$41, 2, FALSE)
Range("Q3").Value = rString
Range("R3").Value = "=P3/$Q$3"
ws1.Range("P3").Copy
ws1.Range("P4:P82").PasteSpecial
ws1.Range("R3").Copy
ws1.Range("R4:R82").PasteSpecial
End Sub