writing macros in excel sheet

[Richard Buttrey]

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
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]

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

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
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]

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
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]

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.

(e-mail address removed)

Rgds

 

[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

"

 

[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