Solved: Lost changes to Workbook/Worksheets

[jwise]

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:

[vba] 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 [/vba]

And here is the Range snippet:

[vba] 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 [/vba]

[majaro]

Can you please post a sample?

[jwise]

It would be extremely difficult to post an example. However, I am going to post a snippet of the "clone" code. I have a new suspicion based on this information: (1) The workbook is "saved" before it has the data added to it. I put this code together from code I found using Google, thus it may be far from optimal. (2) As each worksheet is built, the rows are copied and then the new columns are added. This means that any save done later would "have" to contain the added columns. (3) My conclusion is that the data is being saved in a default location (which I never use) instead of the folder that I'm using for this project. (4) I can beat this problem by doing "SaveAs" after the macro runs.

Note: The purpose of the "If" statements is that all data items have a 2 or 3 character worksheet name, and other worksheets are copied verbatim (no problems here). The selected clone Worksheet (whose name is "APT") is also handled differently as it becomes the basis for all the copies. The actual copy is done on a row by row basis, and only if the "description field" which is in column 1 matches. This means that all the output workbook's worksheets have the same number or rows, but each row of the worksheet's data is only copied if this description matches.

[vba] Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Add

fName = InputBox("Please enter a new Workbook Name.", "Workbook Name")

With wb2
.Title = "PUPA 2007"
.Subject = "Expense/Revenue PUPA Comparison"
.SaveAs fileName:=fName
End With

Workbooks.Open (fName)

Call GetMaxWS(wb1, APT, numRows)

Set wsClone = wb1.Sheets(APT)

For i = 1 To wb1.Sheets.Count
Set ws1 = wb1.Sheets(i)

If ws1.Name = "APT_nums" Then
Set wb2 = Workbooks(fName)
wb1.Sheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)
wb2.Sheets(wb2.Sheets.Count).Name = ws1.Name
GoTo Skip
End If

If Len(ws1.Name) > 3 Then
Set wb2 = Workbooks(fName)
wb1.Sheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)
wb2.Sheets(wb2.Sheets.Count).Name = ws1.Name
GoTo Skip
End If

If ws1.Name = APT Then
Set wb2 = Workbooks(fName)
wb1.Sheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)
wb2.Sheets(wb2.Sheets.Count).Name = ws1.Name
Set ws2 = wb2.Sheets(wb2.Sheets.Count)
Call RngCopy(ws2)
GoTo Skip
End If

wb2.Sheets.Add After:=Sheets(wb2.Sheets.Count) '-- place at end

Set ws2 = wb2.Sheets(wb2.Sheets.Count)

ws2.Name = ws1.Name

For j = 3 To numRows
ws2.Cells(j, 1) = wsClone.Cells(j, 1)
Next j

Call CopyWS(ws1, ws2)
Call RngCopy(ws2)

Skip:

Set ws1 = Nothing
Set ws2 = Nothing

Next i

Set wsClone = Nothing
Set wb1 = Nothing
Set wb2 = Nothing

End Sub [/vba]

[jwise]

There is some kind of strange default action cauing this problem. The files were being saved- I just had difficulty locating them.

Another problem was I could not specify a "drive" and directory" when I created the file. I do not understand this.