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

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

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

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

Appending sheet
 

On Mon, 8 Sep 2008 16:28:21 -0700 (PDT),
wrote:

Thanks Bernie, that worked a treat. Brilliant!

Cheers - Kirk