PDA

View Full Version : Copy from one worksheet and save to another



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

geebra
08-07-2012, 07:46 AM
Heres the sheet i am working on and the cells that I am interested in. I want to pull that cell range and save it on another sheet.

And would it be possible to format so that the 2 columns are side by side?

geebra
08-07-2012, 10:01 AM
I created a macro, how can i incorporate this in to my orginal code?


the Windows("Book1").Activate is what i dont know how to do it, because I am saving on to a different worksheet that automatically opens.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/7/2012 by bgee
'
'
Range("A99:A112").Select
Selection.Copy
Windows("Book1").Activate
Range("I3").Select
ActiveSheet.Paste
Windows("CPLA2012.xls").Activate

'
' Macro2 Macro
' Macro recorded 8/7/2012 by bgee
'
'
Range("J98:J112").Select
Selection.Copy
Windows("Book1").Activate
Range("J2").Select
ActiveSheet.Paste
Windows("CPLA2012.xls").Activate
End Sub