Copy part of worksheet in VBA
 

Ron De Bruin helped me with the following code

Code
-------------------
Sub test()
Dim wb As Workbook
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb.Sheets(1)
.UsedRange.Copy
.UsedRange.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
End Su
-------------------


It copies a worksheet to a seperate file.
But what if I only want to copy C5:K34 to the new file (content no
functions, as above

--
Ruath
-----------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...fo&userid=3108
View this thread: http://www.excelforum.com/showthread.php?threadid=55174

Copy part of worksheet in VBA
 

Hi Ruatha

Then you create a new workbook with one sheet and copy the selection in this sheet
This example save the file in your default folder

Sub Save_Range()
Dim source As Range
Dim dest As Workbook
Dim strdate As String

Set source = Range("C5:K34")

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

strdate = Format(Now, "dd-mm-yy h-mm-ss")
With dest
.SaveAs "Selection of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub

If you want to mail it see
http://www.rondebruin.nl/mail/folder1/mail4.htm

Or
http://www.rondebruin.nl/mail/folder2/mail4.htm




--
Regards Ron De Bruin
http://www.rondebruin.nl



"Ruatha" wrote in message
...

Ron De Bruin helped me with the following code

Code:
--------------------
Sub test()
Dim wb As Workbook
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb.Sheets(1)
.UsedRange.Copy
.UsedRange.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
End Sub
--------------------


It copies a worksheet to a seperate file.
But what if I only want to copy C5:K34 to the new file (content not
functions, as above)


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551749

Copy part of worksheet in VBA
 

Thanks again!!
Will look into that mail page aswell!


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551749

Copy part of worksheet in VBA
 

Hi, I'm here to ask for even more.
Can I get the text in cell "I2" into the filenamne.

As in (approximation) :
Dim Newname as string
Set newname = ActiveWorkBook.Sheets("Jourer").range("I2").Value

and add that to the filename.

Everything I try it says "Object Required (Error 424)"..


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551749

Copy part of worksheet in VBA
 

Btw: You not Set a string

Try this

Sub Save_Range()
Dim source As Range
Dim dest As Workbook
Dim strdate As String
Dim Newname As String

Newname = Sheets("Jourer").Range("I2").Value
Set source = Range("C5:K34") ' this use the cells on the activesheet

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

strdate = Format(Now, "dd-mm-yy h-mm-ss")
With dest
.SaveAs Newname & " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub




--
Regards Ron De Bruin
http://www.rondebruin.nl



"Ruatha" wrote in message
...

Hi, I'm here to ask for even more.
Can I get the text in cell "I2" into the filenamne.

As in (approximation) :
Dim Newname as string
Set newname = ActiveWorkBook.Sheets("Jourer").range("I2").Value

and add that to the filename.

Everything I try it says "Object Required (Error 424)"..


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551749

Copy part of worksheet in VBA
 

You're the best.
I'm learning a lot!!


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551749