Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy part of a worksheet into a worksheet in the same file/keepi. | Excel Worksheet Functions | |||
copy part of row | Excel Programming | |||
copy part of row | Excel Programming | |||
copy part of row | Excel Programming | |||
Copy and Paste part II | Excel Programming |