HI guys
i wonder if anyone can help me. I have written a macro which will export small sections of a large data file and save each section as seperate msdos txt files. the problem is that the macro trys to save each file twice making windows to promt me every time to overite the data. i wouldent mind but i have a huge number of files and the idea behind the macro is to be automated.
can anyone please help as this is really bugging me
' output Macro
' Macro recorded 14/12/2005 by Materials Science Centre
'
' Keyboard Shortcut: Ctrl+o
Dim i As Integer
Dim j As Integer
j = 4
For i = 0 To 1
Set NewBook = Workbooks.Add
fname = "BZN_j"
Windows("6.2conversion.xls").Activate
Range("E7:E4102").Select
Selection.Copy
Windows(2).Activate
ActiveWindow.WindowState = xlNormal
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("6.2conversion.xls").Activate
ActiveWindow.WindowState = xlNormal
Windows("6.2conversion.xls").Activate
Range(Cells(7, 7 + (2 * i)), Cells(4102, 7 + (2 * i))).Select
Application.CutCopyMode = False
Selection.Copy
Windows(2).Activate
ActiveWindow.WindowState = xlNormal
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.DefaultSaveFormat = xlTextMSDOS
ActiveSheet.SaveAs Filename:="BZN" & j, FileFormat:=xlTextMSDOS, CreateBackup:=False
Workbooks(2).Close
j = j + 1
Next i
End Sub
i wonder if anyone can help me. I have written a macro which will export small sections of a large data file and save each section as seperate msdos txt files. the problem is that the macro trys to save each file twice making windows to promt me every time to overite the data. i wouldent mind but i have a huge number of files and the idea behind the macro is to be automated.
can anyone please help as this is really bugging me
' output Macro
' Macro recorded 14/12/2005 by Materials Science Centre
'
' Keyboard Shortcut: Ctrl+o
Dim i As Integer
Dim j As Integer
j = 4
For i = 0 To 1
Set NewBook = Workbooks.Add
fname = "BZN_j"
Windows("6.2conversion.xls").Activate
Range("E7:E4102").Select
Selection.Copy
Windows(2).Activate
ActiveWindow.WindowState = xlNormal
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("6.2conversion.xls").Activate
ActiveWindow.WindowState = xlNormal
Windows("6.2conversion.xls").Activate
Range(Cells(7, 7 + (2 * i)), Cells(4102, 7 + (2 * i))).Select
Application.CutCopyMode = False
Selection.Copy
Windows(2).Activate
ActiveWindow.WindowState = xlNormal
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.DefaultSaveFormat = xlTextMSDOS
ActiveSheet.SaveAs Filename:="BZN" & j, FileFormat:=xlTextMSDOS, CreateBackup:=False
Workbooks(2).Close
j = j + 1
Next i
End Sub