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
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