Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending sheet
Is there a method, either VB or direct mode, to append one sheet to an existing one, including all formatting - particularly font colour and the embedded comments and comment shape/size ? They're currently in separate xls. files. Thanks - Kirk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending sheet
On Sep 8, 12:53*pm, kirkm wrote:
Is there a method, either VB or direct mode, to append one sheet to an existing one, including all formatting - particularly font colour and the embedded *comments and comment shape/size ? They're currently in separate xls. files. Thanks - Kirk Hi Kirk, try the following as a start. You may need to adapt it to your needs. Such as what range you want to copy and where you want to copy to and checking if files are open etc... Sub CopyFromOtherBook() 'This procedure requires the workbooks to be open. 'It is possible to open them programmatically, but that is a different story Dim RangeToBeCopied As Range Dim RangeToCopyTo As Range 'This line sets the range that you want to copy, I have used the .UsedRange property which 'returns a rectangular shaped range that intersects the right-most, left-most, top-most and bottom-most cells ' Ie. the used range...hehe Set RangeToBeCopied = Application.Workbooks("Book1").Worksheets("Sheet1" ).UsedRange 'This line sets the destination to which you wish to paste. This is the Top-Left. Set RangeToCopyTo = Application.Workbooks("Book2").Worksheets("Sheet1" ).Range("A1") 'This bit actually does the copy. RangeToBeCopied.Copy RangeToCopyTo End Sub regards Bernie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending sheet
Thanks Bernie, It looks most ominous but I'll give it a try :) Just one thing - Dim RangeToBeCopied As Range Dim RangeToCopyTo As Range 'This line sets the range that you want to copy, I have used the .UsedRange property which 'returns a rectangular shaped range that intersects the right-most, left-most, top-most and bottom-most cells ' Ie. the used range...hehe Set RangeToBeCopied = Application.Workbooks("Book1").Worksheets("Sheet1 ").UsedRange 'This line sets the destination to which you wish to paste. This is the Top-Left. Set RangeToCopyTo = Application.Workbooks("Book2").Worksheets("Sheet1 ").Range("A1") The "A1" bit (I presume) is the start point and I'd change this to whatever new start point (Top left) I want to use ? It needs no End co-ordinate? Tahnks - Kirk 'This bit actually does the copy. RangeToBeCopied.Copy RangeToCopyTo End Sub regards Bernie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending sheet
On Sep 8, 9:19*pm, kirkm wrote:
Thanks Bernie, It looks most ominous but I'll give it a try :) Just one thing - Dim RangeToBeCopied As Range Dim RangeToCopyTo As Range 'This line sets the range that you want to copy, I have used the .UsedRange property which 'returns a rectangular shaped range that intersects the right-most, left-most, top-most and bottom-most cells ' Ie. the used range...hehe Set RangeToBeCopied = Application.Workbooks("Book1").Worksheets("Sheet1 ").UsedRange 'This line sets the destination to which you wish to paste. This is the Top-Left. Set RangeToCopyTo = Application.Workbooks("Book2").Worksheets("Sheet1 ").Range("A1") The "A1" bit (I presume) is the start point and I'd change this to whatever new start point (Top left) I want to use ? It needs no End co-ordinate? Tahnks - Kirk 'This bit actually does the copy. RangeToBeCopied.Copy RangeToCopyTo End Sub regards Bernie- Hide quoted text - - Show quoted text - :) It wont look so ominous when its in the VBE (the place where you put code). Just copy the code from the line "Sub CopyFromOtherBook()" to "End Sub" and place it in a module within the VBE. Yes change the start co-ordinate to what ever you want. You dont need an end co-ordinate, but thats a good question. Bernie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending sheet
On Mon, 8 Sep 2008 16:28:21 -0700 (PDT),
wrote: Thanks Bernie, that worked a treat. Brilliant! Cheers - Kirk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Appending individual XML files to a sheet | Excel Discussion (Misc queries) | |||
Copy & rename a sheet appending 01,02,03 ..etc | Excel Programming | |||
Appending Data from one sheet to a master sheet | Excel Programming | |||
appending to second worksheet from a Master Sheet | Excel Programming | |||
Appending data from one sheet to another. | Excel Programming |