PDA

View Full Version : Save Workbook Macro Issue



maninjapan
12-03-2013, 07:45 AM
I am using a macro in a main excel to paste data then save and close a number of individul excels.
It works fine for the most part, however I am having the following issue with 2 out of 12 of the individual files.

The workbooks open and paste the data fine, however when the macro attempts to save the workbook, the excel freezes and crashes. If I run the macro line by line, then stop the macro and manually save and close the file I have no issues at all. Below is the macro I run for each sheet. I was wondering if anyone had come across this issue before or had any possible solutions.
The main excel that I am running the macros from is 2.5 meg while the individual files being opened and saved are only about 160kb.

Thanks in advance



Sub SB_COT()
Dim SB_COT As Workbook
Set SB_COT = Workbooks.Open("\\FILE\\COT\SB_COT.xlsx (file://\\FILE\\COT\SB_COT.xlsx)")
ThisWorkbook.Sheets("SB").Range("W3:AG3").Copy
SB_COT.Sheets("data").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

SB_COT.Close SaveChanges:=True

End Sub

SamT
12-03-2013, 04:08 PM
Try this and see if it helps


Sub SB_COT() 'A better name for this sub might be Update_SB_Cot
'
'Changed workbook variable name to prevent any possible VBA confusion with a sub of the same name
Dim wbSB_COT As Workbook
SetwbSB_COT = Workbooks.Open("\\FILE\\COT\SB_COT.xlsx (file://\\FILE\\COT\SB_COT.xlsx)")

ThisWorkbook.Sheets("SB").Range("W3:AG3").Copy

With wbSB_COT
.Sheets("data").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Save
Do Events 'Provide time to save
.Close
End With

End Sub

Using the same name for three different things, a sub, a workbook, and a variable, is a great way to prevent easy code maintenance because it stops all search and replace operations on all of the things with the same name. :devil2: