writing macros in excel sheet

[Yoshitha]

Hi
i'm new to writing macros for an excel file.

i've a macro which will read the data from worksheets and copies that data
and paste the copied data into new workbook sheets.

it is copying the data into new work sheet. whats my problem is my original
application contains formatted data like having cloros and some of the
columns having big font size etc and some of sheets contains merged cells.
i'm totally having 8 worksheets with different layouts and the macro copying
the data into 8 new worksheets but the look and feel is not similar to the
original application. But i want as it is.
cna anyone tell me how to do this
its very urgent for me

thanx in advance
jaffar

[Richard Buttrey]

On Sat, 6 May 2006 13:02:34 -0700, "Yoshitha"
<[email protected]> wrote:

>Hi
>i'm new to writing macros for an excel file.
>
>i've a macro which will read the data from worksheets and copies that data
>and paste the copied data into new workbook sheets.
>
>it is copying the data into new work sheet. whats my problem is my original
>application contains formatted data like having cloros and some of the
>columns having big font size etc and some of sheets contains merged cells.
>i'm totally having 8 worksheets with different layouts and the macro copying
>the data into 8 new worksheets but the look and feel is not similar to the
>original application. But i want as it is.
>cna anyone tell me how to do this
>its very urgent for me
>
>thanx in advance
>jaffar

It sounds like the macro is just copying the values from the original
worksheets, and not the formatting as well.

Look for a line in the macro which says something like

Range("your_range").PasteSpecial (xlPasteValues)

If you want your new sheets to have exactly the same formulae, numbers
and formats as the old sheets, then change that to:

Range("your_range").PasteSpecial (xlPasteAll)

If you want your new worksheets to replace formulae in the old with
just values in the new, add an extra line:

Range("your_range").PasteSpecial (xlPasteFormats)




>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Yoshitha]

Hi Richard

I've used the following one
Range("your_range").PasteSpecial (xlPasteAll)
to paste into new sheet, but in new sheet it is not maintaining the width
and height for the cell, i.e in the original sheet if it is having more
width and in the copied sheet it is not having the same width for a cell.

I want to maintain width and height as it is in original sheet.
and in my original sheet there are some hidden cells, but in my new sheet
those values are displaying but these values must not be shown these must
be hidden.

how can i do this can you please help me.

Thanx
Jaffar


"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> On Sat, 6 May 2006 13:02:34 -0700, "Yoshitha"
> <[email protected]> wrote:
>
> >Hi
> >i'm new to writing macros for an excel file.
> >
> >i've a macro which will read the data from worksheets and copies that
data
> >and paste the copied data into new workbook sheets.
> >
> >it is copying the data into new work sheet. whats my problem is my
original
> >application contains formatted data like having cloros and some of the
> >columns having big font size etc and some of sheets contains merged
cells.
> >i'm totally having 8 worksheets with different layouts and the macro
copying
> >the data into 8 new worksheets but the look and feel is not similar to
the
> >original application. But i want as it is.
> >cna anyone tell me how to do this
> >its very urgent for me
> >
> >thanx in advance
> >jaffar
>
> It sounds like the macro is just copying the values from the original
> worksheets, and not the formatting as well.
>
> Look for a line in the macro which says something like
>
> Range("your_range").PasteSpecial (xlPasteValues)
>
> If you want your new sheets to have exactly the same formulae, numbers
> and formats as the old sheets, then change that to:
>
> Range("your_range").PasteSpecial (xlPasteAll)
>
> If you want your new worksheets to replace formulae in the old with
> just values in the new, add an extra line:
>
> Range("your_range").PasteSpecial (xlPasteFormats)
>
>
>
>
> >
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

[Richard Buttrey]

You could add an additional line

Range("your_range").PasteSpecial (xlPasteColumnWidths)

which would sort out the cell width, but I'm not aware there is an
equivalent for row heights. So you'd need to capture this in a
variable

So for instance for a single cell named "Your_Range1" that you're
copying to a cell named "Your_Range2" somewhere else in the workbook



Sub CopyPasteFormats()
Dim iCellHeight As Integer
iCellHeight = Range("YourRange1").RowHeight
Range("Your_Range1").Copy
Range("Your_Range2").PasteSpecial (xlPasteAll)
Range("Your_Range2").PasteSpecial (xlPasteColumnWidths)
Range("Your_Range2").RowHeight = iCellHeight

End Sub

If you've got several ranges to copy and paste you'd be advised to
create a looping procedure which would pass the range names to the
above procedure. So for instance - untested

Sub CopyFromTo
'Calling macro - only shows one set of ranges for simplicity. Put in a
'loop to selectively call other pairs of ranges

Dim rFrom as Range, rTo as Range

rFrom=Range("Your_Range1")
rTo=Range("Your_Range2")

Call CopyPasteFormats(rFrom,rTo)

End Sub


Sub CopyPasteFormats(rFrom as Range,rTo as Range)
Dim iCellHeight As Integer,
iCellHeight = rFrom.RowHeight
rFrom.Copy
rTo.PasteSpecial (xlPasteAll)
rTo.PasteSpecial (xlPasteColumnWidths)
rTo.RowHeight = iCellHeight

End Sub

HTH


On Sun, 7 May 2006 12:00:36 -0700, "Yoshitha"
<[email protected]> wrote:

>Hi Richard
>
>I've used the following one
>Range("your_range").PasteSpecial (xlPasteAll)
>to paste into new sheet, but in new sheet it is not maintaining the width
>and height for the cell, i.e in the original sheet if it is having more
>width and in the copied sheet it is not having the same width for a cell.
>
>I want to maintain width and height as it is in original sheet.
>and in my original sheet there are some hidden cells, but in my new sheet
>those values are displaying but these values must not be shown these must
>be hidden.
>
>how can i do this can you please help me.
>
>Thanx
>Jaffar
>
>
>"Richard Buttrey" <[email protected]> wrote in
>message news:[email protected]...
>> On Sat, 6 May 2006 13:02:34 -0700, "Yoshitha"
>> <[email protected]> wrote:
>>
>> >Hi
>> >i'm new to writing macros for an excel file.
>> >
>> >i've a macro which will read the data from worksheets and copies that
>data
>> >and paste the copied data into new workbook sheets.
>> >
>> >it is copying the data into new work sheet. whats my problem is my
>original
>> >application contains formatted data like having cloros and some of the
>> >columns having big font size etc and some of sheets contains merged
>cells.
>> >i'm totally having 8 worksheets with different layouts and the macro
>copying
>> >the data into 8 new worksheets but the look and feel is not similar to
>the
>> >original application. But i want as it is.
>> >cna anyone tell me how to do this
>> >its very urgent for me
>> >
>> >thanx in advance
>> >jaffar
>>
>> It sounds like the macro is just copying the values from the original
>> worksheets, and not the formatting as well.
>>
>> Look for a line in the macro which says something like
>>
>> Range("your_range").PasteSpecial (xlPasteValues)
>>
>> If you want your new sheets to have exactly the same formulae, numbers
>> and formats as the old sheets, then change that to:
>>
>> Range("your_range").PasteSpecial (xlPasteAll)
>>
>> If you want your new worksheets to replace formulae in the old with
>> just values in the new, add an extra line:
>>
>> Range("your_range").PasteSpecial (xlPasteFormats)
>>
>>
>>
>>
>> >
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Yoshitha]

hi


i've used the follwoing
PasteSpecial (xlPasteColumnWidths)
and its working fine in my system where in my system office 2003 is
installed and when i tested the same application in other system which is
having office2000 then it is not working it raising error

"pastespecial method of range class failed " and getting error on this line
PasteSpecial (xlPasteColumnWidths)
can you tell me how to solve this problem to work in lower versions also.

One more problem is

after copying the data to new work sheet i've saved the application and then
i opend the saved book (the copied one) then it is not showing the headers
properly it is displaying like
#######
and some places it is showing #Value.
can you telll me how to solve this problem?

Thanx for your help
Jaffer.




"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> You could add an additional line
>
> Range("your_range").PasteSpecial (xlPasteColumnWidths)
>
> which would sort out the cell width, but I'm not aware there is an
> equivalent for row heights. So you'd need to capture this in a
> variable
>
> So for instance for a single cell named "Your_Range1" that you're
> copying to a cell named "Your_Range2" somewhere else in the workbook
>
>
>
> Sub CopyPasteFormats()
> Dim iCellHeight As Integer
> iCellHeight = Range("YourRange1").RowHeight
> Range("Your_Range1").Copy
> Range("Your_Range2").PasteSpecial (xlPasteAll)
> Range("Your_Range2").PasteSpecial (xlPasteColumnWidths)
> Range("Your_Range2").RowHeight = iCellHeight
>
> End Sub
>
> If you've got several ranges to copy and paste you'd be advised to
> create a looping procedure which would pass the range names to the
> above procedure. So for instance - untested
>
> Sub CopyFromTo
> 'Calling macro - only shows one set of ranges for simplicity. Put in a
> 'loop to selectively call other pairs of ranges
>
> Dim rFrom as Range, rTo as Range
>
> rFrom=Range("Your_Range1")
> rTo=Range("Your_Range2")
>
> Call CopyPasteFormats(rFrom,rTo)
>
> End Sub
>
>
> Sub CopyPasteFormats(rFrom as Range,rTo as Range)
> Dim iCellHeight As Integer,
> iCellHeight = rFrom.RowHeight
> rFrom.Copy
> rTo.PasteSpecial (xlPasteAll)
> rTo.PasteSpecial (xlPasteColumnWidths)
> rTo.RowHeight = iCellHeight
>
> End Sub
>
> HTH
>
>
> On Sun, 7 May 2006 12:00:36 -0700, "Yoshitha"
> <[email protected]> wrote:
>
> >Hi Richard
> >
> >I've used the following one
> >Range("your_range").PasteSpecial (xlPasteAll)
> >to paste into new sheet, but in new sheet it is not maintaining the width
> >and height for the cell, i.e in the original sheet if it is having more
> >width and in the copied sheet it is not having the same width for a cell.
> >
> >I want to maintain width and height as it is in original sheet.
> >and in my original sheet there are some hidden cells, but in my new sheet
> >those values are displaying but these values must not be shown these
must
> >be hidden.
> >
> >how can i do this can you please help me.
> >
> >Thanx
> >Jaffar
> >
> >
> >"Richard Buttrey" <[email protected]> wrote in
> >message news:[email protected]...
> >> On Sat, 6 May 2006 13:02:34 -0700, "Yoshitha"
> >> <[email protected]> wrote:
> >>
> >> >Hi
> >> >i'm new to writing macros for an excel file.
> >> >
> >> >i've a macro which will read the data from worksheets and copies that
> >data
> >> >and paste the copied data into new workbook sheets.
> >> >
> >> >it is copying the data into new work sheet. whats my problem is my
> >original
> >> >application contains formatted data like having cloros and some of the
> >> >columns having big font size etc and some of sheets contains merged
> >cells.
> >> >i'm totally having 8 worksheets with different layouts and the macro
> >copying
> >> >the data into 8 new worksheets but the look and feel is not similar to
> >the
> >> >original application. But i want as it is.
> >> >cna anyone tell me how to do this
> >> >its very urgent for me
> >> >
> >> >thanx in advance
> >> >jaffar
> >>
> >> It sounds like the macro is just copying the values from the original
> >> worksheets, and not the formatting as well.
> >>
> >> Look for a line in the macro which says something like
> >>
> >> Range("your_range").PasteSpecial (xlPasteValues)
> >>
> >> If you want your new sheets to have exactly the same formulae, numbers
> >> and formats as the old sheets, then change that to:
> >>
> >> Range("your_range").PasteSpecial (xlPasteAll)
> >>
> >> If you want your new worksheets to replace formulae in the old with
> >> just values in the new, add an extra line:
> >>
> >> Range("your_range").PasteSpecial (xlPasteFormats)
> >>
> >>
> >>
> >>
> >> >
> >>
> >> __
> >> Richard Buttrey
> >> Grappenhall, Cheshire, UK
> >> __________________________
> >
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

[Richard Buttrey]

On Mon, 8 May 2006 18:46:51 -0700, "Yoshitha"
<[email protected]> wrote:

>hi
>
>
>i've used the follwoing
> PasteSpecial (xlPasteColumnWidths)
>and its working fine in my system where in my system office 2003 is
>installed and when i tested the same application in other system which is
>having office2000 then it is not working it raising error


>"pastespecial method of range class failed " and getting error on this line
> PasteSpecial (xlPasteColumnWidths)
>can you tell me how to solve this problem to work in lower versions also.

OK.

ISTR that there was a bug associated with ColumnWidths in earlier
versions of Excel.

try instead of
..PasteSpecial (xlPasteColumnWidths)

..PasteSpecial Paste:=8



>One more problem is
>
>after copying the data to new work sheet i've saved the application and then
>i opend the saved book (the copied one) then it is not showing the headers
>properly it is displaying like
>#######

The ####### suggests that the column is not wide enough to display one
of the data dells.

Include the cell with that data as one of your sets of Your_Range1,
Your_Range2 Copy and PasteSpecial.Paste:=8 lines of code.

Thinking about this, it might be better just to build a procedure
that copies and sets all the column widths and row heights in Book2
based on their row and height values in Book1. If you're not wanting
to copy colours and fonts etc. then this would be all you need.


>and some places it is showing #Value.
>can you telll me how to solve this problem?

The #Value suggests your copying a formula which can't be evaluated in
Book 2. If you're only wanting the value and not the formulam then
make sure you're using the

..PasteSpecial (xlPasteValues)

Rgds,



>Thanx for your help
>Jaffer.
>
>
>
>
>"Richard Buttrey" <[email protected]> wrote in
>message news:[email protected]...
>> You could add an additional line
>>
>> Range("your_range").PasteSpecial (xlPasteColumnWidths)
>>
>> which would sort out the cell width, but I'm not aware there is an
>> equivalent for row heights. So you'd need to capture this in a
>> variable
>>
>> So for instance for a single cell named "Your_Range1" that you're
>> copying to a cell named "Your_Range2" somewhere else in the workbook
>>
>>
>>
>> Sub CopyPasteFormats()
>> Dim iCellHeight As Integer
>> iCellHeight = Range("YourRange1").RowHeight
>> Range("Your_Range1").Copy
>> Range("Your_Range2").PasteSpecial (xlPasteAll)
>> Range("Your_Range2").PasteSpecial (xlPasteColumnWidths)
>> Range("Your_Range2").RowHeight = iCellHeight
>>
>> End Sub
>>
>> If you've got several ranges to copy and paste you'd be advised to
>> create a looping procedure which would pass the range names to the
>> above procedure. So for instance - untested
>>
>> Sub CopyFromTo
>> 'Calling macro - only shows one set of ranges for simplicity. Put in a
>> 'loop to selectively call other pairs of ranges
>>
>> Dim rFrom as Range, rTo as Range
>>
>> rFrom=Range("Your_Range1")
>> rTo=Range("Your_Range2")
>>
>> Call CopyPasteFormats(rFrom,rTo)
>>
>> End Sub
>>
>>
>> Sub CopyPasteFormats(rFrom as Range,rTo as Range)
>> Dim iCellHeight As Integer,
>> iCellHeight = rFrom.RowHeight
>> rFrom.Copy
>> rTo.PasteSpecial (xlPasteAll)
>> rTo.PasteSpecial (xlPasteColumnWidths)
>> rTo.RowHeight = iCellHeight
>>
>> End Sub
>>
>> HTH
>>
>>
>> On Sun, 7 May 2006 12:00:36 -0700, "Yoshitha"
>> <[email protected]> wrote:
>>
>> >Hi Richard
>> >
>> >I've used the following one
>> >Range("your_range").PasteSpecial (xlPasteAll)
>> >to paste into new sheet, but in new sheet it is not maintaining the width
>> >and height for the cell, i.e in the original sheet if it is having more
>> >width and in the copied sheet it is not having the same width for a cell.
>> >
>> >I want to maintain width and height as it is in original sheet.
>> >and in my original sheet there are some hidden cells, but in my new sheet
>> >those values are displaying but these values must not be shown these
>must
>> >be hidden.
>> >
>> >how can i do this can you please help me.
>> >
>> >Thanx
>> >Jaffar
>> >
>> >
>> >"Richard Buttrey" <[email protected]> wrote in
>> >message news:[email protected]...
>> >> On Sat, 6 May 2006 13:02:34 -0700, "Yoshitha"
>> >> <[email protected]> wrote:
>> >>
>> >> >Hi
>> >> >i'm new to writing macros for an excel file.
>> >> >
>> >> >i've a macro which will read the data from worksheets and copies that
>> >data
>> >> >and paste the copied data into new workbook sheets.
>> >> >
>> >> >it is copying the data into new work sheet. whats my problem is my
>> >original
>> >> >application contains formatted data like having cloros and some of the
>> >> >columns having big font size etc and some of sheets contains merged
>> >cells.
>> >> >i'm totally having 8 worksheets with different layouts and the macro
>> >copying
>> >> >the data into 8 new worksheets but the look and feel is not similar to
>> >the
>> >> >original application. But i want as it is.
>> >> >cna anyone tell me how to do this
>> >> >its very urgent for me
>> >> >
>> >> >thanx in advance
>> >> >jaffar
>> >>
>> >> It sounds like the macro is just copying the values from the original
>> >> worksheets, and not the formatting as well.
>> >>
>> >> Look for a line in the macro which says something like
>> >>
>> >> Range("your_range").PasteSpecial (xlPasteValues)
>> >>
>> >> If you want your new sheets to have exactly the same formulae, numbers
>> >> and formats as the old sheets, then change that to:
>> >>
>> >> Range("your_range").PasteSpecial (xlPasteAll)
>> >>
>> >> If you want your new worksheets to replace formulae in the old with
>> >> just values in the new, add an extra line:
>> >>
>> >> Range("your_range").PasteSpecial (xlPasteFormats)
>> >>
>> >>
>> >>
>> >>
>> >> >
>> >>
>> >> __
>> >> Richard Buttrey
>> >> Grappenhall, Cheshire, UK
>> >> __________________________
>> >
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Yoshitha]

Hi Richard

thank you very much, now i solved the problem with the help of your
assistance. i also used xlPasteFormats to include formats.

i've another problem
in original sheet some of the cells having no borders at all i mean it look
like white background, after copying the data into new sheet i'm not getting
white background i.e it looks like cells. I hope you understand what i said
now.











in my new sheet cells looking like this where as in original sheet does not
have borders for a cell (totally it looks like white background)

thanx
Jaffer

"

Richard Buttrey" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 8 May 2006 18:46:51 -0700, "Yoshitha"
> <[email protected]> wrote:
>
> >hi
> >
> >
> >i've used the follwoing
> > PasteSpecial (xlPasteColumnWidths)
> >and its working fine in my system where in my system office 2003 is
> >installed and when i tested the same application in other system which is
> >having office2000 then it is not working it raising error
>
>
> >"pastespecial method of range class failed " and getting error on this
line
> > PasteSpecial (xlPasteColumnWidths)
> >can you tell me how to solve this problem to work in lower versions also.
>
> OK.
>
> ISTR that there was a bug associated with ColumnWidths in earlier
> versions of Excel.
>
> try instead of
> .PasteSpecial (xlPasteColumnWidths)
>
> .PasteSpecial Paste:=8
>
>
>
> >One more problem is
> >
> >after copying the data to new work sheet i've saved the application and
then
> >i opend the saved book (the copied one) then it is not showing the
headers
> >properly it is displaying like
> >#######
>
> The ####### suggests that the column is not wide enough to display one
> of the data dells.
>
> Include the cell with that data as one of your sets of Your_Range1,
> Your_Range2 Copy and PasteSpecial.Paste:=8 lines of code.
>
> Thinking about this, it might be better just to build a procedure
> that copies and sets all the column widths and row heights in Book2
> based on their row and height values in Book1. If you're not wanting
> to copy colours and fonts etc. then this would be all you need.
>
>
> >and some places it is showing #Value.
> >can you telll me how to solve this problem?
>
> The #Value suggests your copying a formula which can't be evaluated in
> Book 2. If you're only wanting the value and not the formulam then
> make sure you're using the
>
> .PasteSpecial (xlPasteValues)
>
> Rgds,
>
>
>
> >Thanx for your help
> >Jaffer.
> >
> >
> >
> >
> >"Richard Buttrey" <[email protected]> wrote in
> >message news:[email protected]...
> >> You could add an additional line
> >>
> >> Range("your_range").PasteSpecial (xlPasteColumnWidths)
> >>
> >> which would sort out the cell width, but I'm not aware there is an
> >> equivalent for row heights. So you'd need to capture this in a
> >> variable
> >>
> >> So for instance for a single cell named "Your_Range1" that you're
> >> copying to a cell named "Your_Range2" somewhere else in the workbook
> >>
> >>
> >>
> >> Sub CopyPasteFormats()
> >> Dim iCellHeight As Integer
> >> iCellHeight = Range("YourRange1").RowHeight
> >> Range("Your_Range1").Copy
> >> Range("Your_Range2").PasteSpecial (xlPasteAll)
> >> Range("Your_Range2").PasteSpecial (xlPasteColumnWidths)
> >> Range("Your_Range2").RowHeight = iCellHeight
> >>
> >> End Sub
> >>
> >> If you've got several ranges to copy and paste you'd be advised to
> >> create a looping procedure which would pass the range names to the
> >> above procedure. So for instance - untested
> >>
> >> Sub CopyFromTo
> >> 'Calling macro - only shows one set of ranges for simplicity. Put in a
> >> 'loop to selectively call other pairs of ranges
> >>
> >> Dim rFrom as Range, rTo as Range
> >>
> >> rFrom=Range("Your_Range1")
> >> rTo=Range("Your_Range2")
> >>
> >> Call CopyPasteFormats(rFrom,rTo)
> >>
> >> End Sub
> >>
> >>
> >> Sub CopyPasteFormats(rFrom as Range,rTo as Range)
> >> Dim iCellHeight As Integer,
> >> iCellHeight = rFrom.RowHeight
> >> rFrom.Copy
> >> rTo.PasteSpecial (xlPasteAll)
> >> rTo.PasteSpecial (xlPasteColumnWidths)
> >> rTo.RowHeight = iCellHeight
> >>
> >> End Sub
> >>
> >> HTH
> >>
> >>
> >> On Sun, 7 May 2006 12:00:36 -0700, "Yoshitha"
> >> <[email protected]> wrote:
> >>
> >> >Hi Richard
> >> >
> >> >I've used the following one
> >> >Range("your_range").PasteSpecial (xlPasteAll)
> >> >to paste into new sheet, but in new sheet it is not maintaining the
width
> >> >and height for the cell, i.e in the original sheet if it is having
more
> >> >width and in the copied sheet it is not having the same width for a
cell.
> >> >
> >> >I want to maintain width and height as it is in original sheet.
> >> >and in my original sheet there are some hidden cells, but in my new
sheet
> >> >those values are displaying but these values must not be shown these
> >must
> >> >be hidden.
> >> >
> >> >how can i do this can you please help me.
> >> >
> >> >Thanx
> >> >Jaffar
> >> >
> >> >
> >> >"Richard Buttrey" <[email protected]> wrote in
> >> >message news:[email protected]...
> >> >> On Sat, 6 May 2006 13:02:34 -0700, "Yoshitha"
> >> >> <[email protected]> wrote:
> >> >>
> >> >> >Hi
> >> >> >i'm new to writing macros for an excel file.
> >> >> >
> >> >> >i've a macro which will read the data from worksheets and copies
that
> >> >data
> >> >> >and paste the copied data into new workbook sheets.
> >> >> >
> >> >> >it is copying the data into new work sheet. whats my problem is my
> >> >original
> >> >> >application contains formatted data like having cloros and some of
the
> >> >> >columns having big font size etc and some of sheets contains merged
> >> >cells.
> >> >> >i'm totally having 8 worksheets with different layouts and the
macro
> >> >copying
> >> >> >the data into 8 new worksheets but the look and feel is not similar
to
> >> >the
> >> >> >original application. But i want as it is.
> >> >> >cna anyone tell me how to do this
> >> >> >its very urgent for me
> >> >> >
> >> >> >thanx in advance
> >> >> >jaffar
> >> >>
> >> >> It sounds like the macro is just copying the values from the
original
> >> >> worksheets, and not the formatting as well.
> >> >>
> >> >> Look for a line in the macro which says something like
> >> >>
> >> >> Range("your_range").PasteSpecial (xlPasteValues)
> >> >>
> >> >> If you want your new sheets to have exactly the same formulae,
numbers
> >> >> and formats as the old sheets, then change that to:
> >> >>
> >> >> Range("your_range").PasteSpecial (xlPasteAll)
> >> >>
> >> >> If you want your new worksheets to replace formulae in the old with
> >> >> just values in the new, add an extra line:
> >> >>
> >> >> Range("your_range").PasteSpecial (xlPasteFormats)
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> >
> >> >>
> >> >> __
> >> >> Richard Buttrey
> >> >> Grappenhall, Cheshire, UK
> >> >> __________________________
> >> >
> >>
> >> __
> >> Richard Buttrey
> >> Grappenhall, Cheshire, UK
> >> __________________________
> >
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

[Yoshitha]

Hi
can you clarify some of my doubts

1. we are using pastespecial paste:xlPasteValues to paste the values into
new worksheet can you tell me whether it is possible to copy the data into
new worksheet without using paste method ? I heard that it is possible
using windows clipboard API. if it is possible can you tell me how to do
this?

2. In my workbook there are 9 sheets with different formats different ranges
and each seet having some hidden rows.

is it possible to copy all these 9 sheets data into a single work bokk sheet
one below the other?

if it possible can you provide sample code for me.

its very very urgent for me

Thanx
Jaffer





"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> On Mon, 8 May 2006 18:46:51 -0700, "Yoshitha"
> <[email protected]> wrote:
>
> >hi
> >
> >
> >i've used the follwoing
> > PasteSpecial (xlPasteColumnWidths)
> >and its working fine in my system where in my system office 2003 is
> >installed and when i tested the same application in other system which is
> >having office2000 then it is not working it raising error
>
>
> >"pastespecial method of range class failed " and getting error on this
line
> > PasteSpecial (xlPasteColumnWidths)
> >can you tell me how to solve this problem to work in lower versions also.
>
> OK.
>
> ISTR that there was a bug associated with ColumnWidths in earlier
> versions of Excel.
>
> try instead of
> .PasteSpecial (xlPasteColumnWidths)
>
> .PasteSpecial Paste:=8
>
>
>
> >One more problem is
> >
> >after copying the data to new work sheet i've saved the application and
then
> >i opend the saved book (the copied one) then it is not showing the
headers
> >properly it is displaying like
> >#######
>
> The ####### suggests that the column is not wide enough to display one
> of the data dells.
>
> Include the cell with that data as one of your sets of Your_Range1,
> Your_Range2 Copy and PasteSpecial.Paste:=8 lines of code.
>
> Thinking about this, it might be better just to build a procedure
> that copies and sets all the column widths and row heights in Book2
> based on their row and height values in Book1. If you're not wanting
> to copy colours and fonts etc. then this would be all you need.
>
>
> >and some places it is showing #Value.
> >can you telll me how to solve this problem?
>
> The #Value suggests your copying a formula which can't be evaluated in
> Book 2. If you're only wanting the value and not the formulam then
> make sure you're using the
>
> .PasteSpecial (xlPasteValues)
>
> Rgds,
>
>
>
> >Thanx for your help
> >Jaffer.
> >
> >
> >
> >
> >"Richard Buttrey" <[email protected]> wrote in
> >message news:[email protected]...
> >> You could add an additional line
> >>
> >> Range("your_range").PasteSpecial (xlPasteColumnWidths)
> >>
> >> which would sort out the cell width, but I'm not aware there is an
> >> equivalent for row heights. So you'd need to capture this in a
> >> variable
> >>
> >> So for instance for a single cell named "Your_Range1" that you're
> >> copying to a cell named "Your_Range2" somewhere else in the workbook
> >>
> >>
> >>
> >> Sub CopyPasteFormats()
> >> Dim iCellHeight As Integer
> >> iCellHeight = Range("YourRange1").RowHeight
> >> Range("Your_Range1").Copy
> >> Range("Your_Range2").PasteSpecial (xlPasteAll)
> >> Range("Your_Range2").PasteSpecial (xlPasteColumnWidths)
> >> Range("Your_Range2").RowHeight = iCellHeight
> >>
> >> End Sub
> >>
> >> If you've got several ranges to copy and paste you'd be advised to
> >> create a looping procedure which would pass the range names to the
> >> above procedure. So for instance - untested
> >>
> >> Sub CopyFromTo
> >> 'Calling macro - only shows one set of ranges for simplicity. Put in a
> >> 'loop to selectively call other pairs of ranges
> >>
> >> Dim rFrom as Range, rTo as Range
> >>
> >> rFrom=Range("Your_Range1")
> >> rTo=Range("Your_Range2")
> >>
> >> Call CopyPasteFormats(rFrom,rTo)
> >>
> >> End Sub
> >>
> >>
> >> Sub CopyPasteFormats(rFrom as Range,rTo as Range)
> >> Dim iCellHeight As Integer,
> >> iCellHeight = rFrom.RowHeight
> >> rFrom.Copy
> >> rTo.PasteSpecial (xlPasteAll)
> >> rTo.PasteSpecial (xlPasteColumnWidths)
> >> rTo.RowHeight = iCellHeight
> >>
> >> End Sub
> >>
> >> HTH
> >>
> >>
> >> On Sun, 7 May 2006 12:00:36 -0700, "Yoshitha"
> >> <[email protected]> wrote:
> >>
> >> >Hi Richard
> >> >
> >> >I've used the following one
> >> >Range("your_range").PasteSpecial (xlPasteAll)
> >> >to paste into new sheet, but in new sheet it is not maintaining the
width
> >> >and height for the cell, i.e in the original sheet if it is having
more
> >> >width and in the copied sheet it is not having the same width for a
cell.
> >> >
> >> >I want to maintain width and height as it is in original sheet.
> >> >and in my original sheet there are some hidden cells, but in my new
sheet
> >> >those values are displaying but these values must not be shown these
> >must
> >> >be hidden.
> >> >
> >> >how can i do this can you please help me.
> >> >
> >> >Thanx
> >> >Jaffar
> >> >
> >> >
> >> >"Richard Buttrey" <[email protected]> wrote in
> >> >message news:[email protected]...
> >> >> On Sat, 6 May 2006 13:02:34 -0700, "Yoshitha"
> >> >> <[email protected]> wrote:
> >> >>
> >> >> >Hi
> >> >> >i'm new to writing macros for an excel file.
> >> >> >
> >> >> >i've a macro which will read the data from worksheets and copies
that
> >> >data
> >> >> >and paste the copied data into new workbook sheets.
> >> >> >
> >> >> >it is copying the data into new work sheet. whats my problem is my
> >> >original
> >> >> >application contains formatted data like having cloros and some of
the
> >> >> >columns having big font size etc and some of sheets contains merged
> >> >cells.
> >> >> >i'm totally having 8 worksheets with different layouts and the
macro
> >> >copying
> >> >> >the data into 8 new worksheets but the look and feel is not similar
to
> >> >the
> >> >> >original application. But i want as it is.
> >> >> >cna anyone tell me how to do this
> >> >> >its very urgent for me
> >> >> >
> >> >> >thanx in advance
> >> >> >jaffar
> >> >>
> >> >> It sounds like the macro is just copying the values from the
original
> >> >> worksheets, and not the formatting as well.
> >> >>
> >> >> Look for a line in the macro which says something like
> >> >>
> >> >> Range("your_range").PasteSpecial (xlPasteValues)
> >> >>
> >> >> If you want your new sheets to have exactly the same formulae,
numbers
> >> >> and formats as the old sheets, then change that to:
> >> >>
> >> >> Range("your_range").PasteSpecial (xlPasteAll)
> >> >>
> >> >> If you want your new worksheets to replace formulae in the old with
> >> >> just values in the new, add an extra line:
> >> >>
> >> >> Range("your_range").PasteSpecial (xlPasteFormats)
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> >
> >> >>
> >> >> __
> >> >> Richard Buttrey
> >> >> Grappenhall, Cheshire, UK
> >> >> __________________________
> >> >
> >>
> >> __
> >> Richard Buttrey
> >> Grappenhall, Cheshire, UK
> >> __________________________
> >
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

[Richard]

Yoshitha wrote:
> Hi Richard
>
> thank you very much, now i solved the problem with the help of your
> assistance. i also used xlPasteFormats to include formats.
>
> i've another problem
> in original sheet some of the cells having no borders at all i mean it look
> like white background, after copying the data into new sheet i'm not getting
> white background i.e it looks like cells. I hope you understand what i said
> now.

Hi,

I suspect that what you're looking at in the new sheet is not a border
around a cell, but the Excel gridlines, which look like a border, but
aren't lines which belong to the cell and wouldn't be printed.

In the second sheet try this first to see if it gives you the white
background you're looking for.

Tools--->Options---> View Tab and then untick the gridlines option.

If that isn't the answer and there are border lines in the cell, add
this extra bit of code like you have done with the others.

Range("Your_Range2").Borders.LineStyle = xlNone

If you don't want borders anwhere on the new sheet, and to avoid
applying that line of code to several ranges, just change it to:

Worksheets("Sheet1").Cells.Borders.LineStyle = xlNone

obviously change the reference to 'Sheet1' to whatever your sheet is
called

HTH

[Richard]

Hi,

Copying and Pasting does use the Windows Clipboard.

Have you considered just linking cells in the second workbook to all
the relevant cells in the first workbook? That would avoid you having
to copy and paste anything, and would mean that workbook 2 is
dynamically updated every time a change is made to book1.

If you want to continue with the copy and paste, then it would be
preferable if you could email your workbook to me - taking out or
changing any sensitive data of course.

[email protected]

Rgds