Hiya,
So I've found the following code from another thread, and adapted it:
However, I want it to just copy cells A1:AL198 as below line 198, is a load of calculations to get the top bit working. Can anyone help me with this?
So I've found the following code from another thread, and adapted it:
VBA Code:
Sub SaveAsValues()
Dim NewBook As Workbook
Dim CurrentFile As String
Dim NewFile As String
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Activate
Range("A1:AL198").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Next i
ActiveWorkbook.Worksheets(1).Activate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
CurrentFile = ActiveWorkbook.FullName
NewFile = "TNew Worksheet"
ActiveWorkbook.SaveAs Filename:="C:\Test\" & NewFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
Set NewBook = ActiveWorkbook
Workbooks.Open CurrentFile
NewBook.Close
Application.ScreenUpdating = True
End Sub
However, I want it to just copy cells A1:AL198 as below line 198, is a load of calculations to get the top bit working. Can anyone help me with this?