Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Function Question
Hello Everyone,
I have VBA code that takes a sheet and copies it, and then formats it to what I need. But, the copy method that I use, is copying it to a new book with the same sheet name. ActiveSheet.Copy Is there a way to copy the contents into the same book but to a different sheet name instead of copying it to a completely new book?? For example: I want to copy the contents of sheet "paste" into sheet "format". Thanks, Mikey Vaughan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Function Question
Simply declare Befo
ActiveSheet.Copy Befo=Sheets("Paste") ActiveSheet.Name = "format" HTH Die_Another_Day Michael Vaughan wrote: Hello Everyone, I have VBA code that takes a sheet and copies it, and then formats it to what I need. But, the copy method that I use, is copying it to a new book with the same sheet name. ActiveSheet.Copy Is there a way to copy the contents into the same book but to a different sheet name instead of copying it to a completely new book?? For example: I want to copy the contents of sheet "paste" into sheet "format". Thanks, Mikey Vaughan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Function Question
Hi HTH,
Well, that works kind of. It will make up a new sheet called "format". But, I wanted to copy it into a sheet that already exits called "format". With the commands you gave me, it errors out and says, "Cannot rename sheet to the same name as another sheet". If the sheet does not exist, then it works. But I wanted to copy it into a sheet that already exits, "format". Thanks, Mikey Vaughan "Die_Another_Day" wrote in message ups.com... Simply declare Befo ActiveSheet.Copy Befo=Sheets("Paste") ActiveSheet.Name = "format" HTH Die_Another_Day Michael Vaughan wrote: Hello Everyone, I have VBA code that takes a sheet and copies it, and then formats it to what I need. But, the copy method that I use, is copying it to a new book with the same sheet name. ActiveSheet.Copy Is there a way to copy the contents into the same book but to a different sheet name instead of copying it to a completely new book?? For example: I want to copy the contents of sheet "paste" into sheet "format". Thanks, Mikey Vaughan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Function Question
Activesheet.cells.copy _
destination:=worksheets("Format").range("a1") ===== But you may want to just delete the current sheet named Format, and copy the sheet???? Michael Vaughan wrote: Hello Everyone, I have VBA code that takes a sheet and copies it, and then formats it to what I need. But, the copy method that I use, is copying it to a new book with the same sheet name. ActiveSheet.Copy Is there a way to copy the contents into the same book but to a different sheet name instead of copying it to a completely new book?? For example: I want to copy the contents of sheet "paste" into sheet "format". Thanks, Mikey Vaughan -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Function Question
Doh!!
if you want formulas copied then Function FindLastCell() As Range Dim LastColumn As Integer Dim LastRow As Long Dim LastCell As Range If WorksheetFunction.CountA(Cells) 0 Then 'Search for any entry, by searching backwards by Rows. LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row 'Search for any entry, by searching backwards by Columns. LastColumn = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column Set FindLastCell = Cells(LastRow, LastColumn) Else Set FindLastCell = Range("A1") End If End Function Sub CopySheet() Dim lCell as Range 'LastCell Set lCell = FindLastCell Sheets("Paste").Activate Range("A1",lCell).Copy Sheets("format").Activate Range("A1").PasteSpecial xlPasteAll End Sub If you only want to paste Values change xlPasteAll to xlPasteValues HTH Die_Another_Day P.S. Sry 'bout the misunderstanding Michael Vaughan wrote: Hi HTH, Well, that works kind of. It will make up a new sheet called "format". But, I wanted to copy it into a sheet that already exits called "format". With the commands you gave me, it errors out and says, "Cannot rename sheet to the same name as another sheet". If the sheet does not exist, then it works. But I wanted to copy it into a sheet that already exits, "format". Thanks, Mikey Vaughan "Die_Another_Day" wrote in message ups.com... Simply declare Befo ActiveSheet.Copy Befo=Sheets("Paste") ActiveSheet.Name = "format" HTH Die_Another_Day Michael Vaughan wrote: Hello Everyone, I have VBA code that takes a sheet and copies it, and then formats it to what I need. But, the copy method that I use, is copying it to a new book with the same sheet name. ActiveSheet.Copy Is there a way to copy the contents into the same book but to a different sheet name instead of copying it to a completely new book?? For example: I want to copy the contents of sheet "paste" into sheet "format". Thanks, Mikey Vaughan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Excel Function from a Cell Through a Function | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
Hyperlink function and copy the cells without the function | Excel Programming | |||
copy question | Excel Programming | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |