Appending sheet

[kirkm]

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

 

[imageswords.br]

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

 

[kirkm]

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

 

[imageswords.br]

Thanks Bernie,

It looks most ominous but I'll give it a try
Just one thing -


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






- 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

 

[kirkm]

Thanks Bernie, that worked a treat. Brilliant!

Cheers - Kirk