geebra
08-07-2012, 07:36 AM
Hi guys, I have more probs, I pasted what you guys suggested on on my last post and pasted in to what I already have.
Sheets(1).Range("A99:A112").Copy Sheets(2).Range("A6")
Sheets(1).Range("J98:J112").Copy Sheets(2).Range("J5")
Sheets(2).Copy
I get a error saying out of range and its becase of the Sheet(2), can you guys tell me what to change to fix it?
Is there a better way, on that excel i have sheets 1 to 10 and it would overwrite the info i have on sheet 2. Can I just copy those cell ranges without affecting the rest of my data?
Thanks
'Global Variables
Dim Heading() As String
Sub a()
Dim NumCol As Integer
NumCol = 1
ReDim Heading(NumCol - 1) As String
NewTitle = "CPLA"
GapTitle = "Gas and Liquid Analysis"
Monthdate = ActiveSheet.Name
SaveDirectory = "H:\"
Sheets(1).Range("A99:A112").Copy Sheets(2).Range("A6")
Sheets(1).Range("J98:J112").Copy Sheets(2).Range("J5")
Sheets(2).Copy
'Creates the titles
ActiveSheet.Name = "Data"
Worksheets("Data").Range("A:S").ColumnWidth = 15
Worksheets("Data").Range("A1").Value = NewTitle
Worksheets("Data").Range("A3").Value = GapTitle
Worksheets("Data").Range("D3").Value = Monthdate
'If not saved then opens and prompts
Test = False
On Error GoTo NotSaved
ActiveWorkbook.SaveAs Filename:=SaveDirectory & "CPLA Atlagas GAP " & Monthdate & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Test = True
NotSaved:
If Test = False Then
MsgBox "The new file was not saved but has been left open for you.", vbOKOnly, "New File dit not Save"
Else
ActiveWorkbook.Close
End If
End Sub
Sheets(1).Range("A99:A112").Copy Sheets(2).Range("A6")
Sheets(1).Range("J98:J112").Copy Sheets(2).Range("J5")
Sheets(2).Copy
I get a error saying out of range and its becase of the Sheet(2), can you guys tell me what to change to fix it?
Is there a better way, on that excel i have sheets 1 to 10 and it would overwrite the info i have on sheet 2. Can I just copy those cell ranges without affecting the rest of my data?
Thanks
'Global Variables
Dim Heading() As String
Sub a()
Dim NumCol As Integer
NumCol = 1
ReDim Heading(NumCol - 1) As String
NewTitle = "CPLA"
GapTitle = "Gas and Liquid Analysis"
Monthdate = ActiveSheet.Name
SaveDirectory = "H:\"
Sheets(1).Range("A99:A112").Copy Sheets(2).Range("A6")
Sheets(1).Range("J98:J112").Copy Sheets(2).Range("J5")
Sheets(2).Copy
'Creates the titles
ActiveSheet.Name = "Data"
Worksheets("Data").Range("A:S").ColumnWidth = 15
Worksheets("Data").Range("A1").Value = NewTitle
Worksheets("Data").Range("A3").Value = GapTitle
Worksheets("Data").Range("D3").Value = Monthdate
'If not saved then opens and prompts
Test = False
On Error GoTo NotSaved
ActiveWorkbook.SaveAs Filename:=SaveDirectory & "CPLA Atlagas GAP " & Monthdate & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Test = True
NotSaved:
If Test = False Then
MsgBox "The new file was not saved but has been left open for you.", vbOKOnly, "New File dit not Save"
Else
ActiveWorkbook.Close
End If
End Sub