#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Susan
 
Posts: n/a
Default Row Expansion

Is there anyway to set a row to automatically expand when info is transferred
to it from another sheet?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach
 
Posts: n/a
Default Row Expansion

If you set the destination cell to Wrap, the cell will automatically
increase/decrease in height to accommodate the quantity of text in the cell.
Is that what you wanted?
If you want the column width to change automatically, you will need an event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Susan
 
Posts: n/a
Default Row Expansion

I checked my cells formatting and the text wrapping option is selected but
the row does not increase in height when text is transferred. If I do a
column expansion, I'm afraid my spreadsheet will exceed my margins. Is there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will automatically
increase/decrease in height to accommodate the quantity of text in the cell.
Is that what you wanted?
If you want the column width to change automatically, you will need an event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach
 
Posts: n/a
Default Row Expansion

Susan
Your right. When you copy data from a cell and paste it into a cell
that is formatted for Wrap, the format of the source cell comes along with
the paste and you lose the Wrap formatting. You can manually set the format
of that cell back to Wrap or you can run the following macro with that cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into which you
will be pasting the data. Something like "any cell in Column A" or "any
cell in Column A from this row to that row". Or any other column or groups
of columns and groups of rows. If you can nail down the range into which
you will be pasting, then when you paste into any cell in that range, the
format will be changed to Wrap. Post back if you think something like this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is selected but
the row does not increase in height when text is transferred. If I do a
column expansion, I'm afraid my spreadsheet will exceed my margins. Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will automatically
increase/decrease in height to accommodate the quantity of text in the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will need an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Susan
 
Posts: n/a
Default Row Expansion

Otto,

The source cell from where I am transferring data is already set to word
wrap. I need a macro that when the data is transferred to the destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it into a cell
that is formatted for Wrap, the format of the source cell comes along with
the paste and you lose the Wrap formatting. You can manually set the format
of that cell back to Wrap or you can run the following macro with that cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into which you
will be pasting the data. Something like "any cell in Column A" or "any
cell in Column A from this row to that row". Or any other column or groups
of columns and groups of rows. If you can nail down the range into which
you will be pasting, then when you paste into any cell in that range, the
format will be changed to Wrap. Post back if you think something like this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is selected but
the row does not increase in height when text is transferred. If I do a
column expansion, I'm afraid my spreadsheet will exceed my margins. Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will automatically
increase/decrease in height to accommodate the quantity of text in the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will need an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach
 
Posts: n/a
Default Row Expansion

Susan
I don't know what you have in your file for it to act this way. If the
source cell is formatted for Wrap and you copy it and paste it into any
cell, that cell (the destination cell) will be formatted for Wrap regardless
of its format before the paste. You say that is not happening for you.
Yes, there is a way to automatically format a cell immediately after you
paste into it. You would need a Worksheet_Change event macro. Such a macro
fires (executes) whenever the content of any cell in the entire sheet
changes. You would need to write the code of the macro to take the action
you want (format to wrap) whenever any cell within your range changes. I'm
sure you don't want this macro to take the Wrap action when any cell in the
entire sheet changes. Can you specify the range in which you want this
action to occur? Like "Anywhere in Column A from row 3 to row 150". Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever range you
come up with, you need to name it. Say "TheRange". The following macro
will change the format of the cell to Wrap when the changed cell is within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It MUST be
placed in the sheet module for the sheet in which you want this to happen.
To access that module, right-click on the sheet tab of your sheet. From the
menu that pops up select View Code. Paste this macro into that module.
Click on the "X" in the top right corner of the module to return to your
sheet. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Otto,

The source cell from where I am transferring data is already set to word
wrap. I need a macro that when the data is transferred to the
destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it into a cell
that is formatted for Wrap, the format of the source cell comes along
with
the paste and you lose the Wrap formatting. You can manually set the
format
of that cell back to Wrap or you can run the following macro with that
cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into which
you
will be pasting the data. Something like "any cell in Column A" or "any
cell in Column A from this row to that row". Or any other column or
groups
of columns and groups of rows. If you can nail down the range into which
you will be pasting, then when you paste into any cell in that range, the
format will be changed to Wrap. Post back if you think something like
this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is selected
but
the row does not increase in height when text is transferred. If I do
a
column expansion, I'm afraid my spreadsheet will exceed my margins. Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will automatically
increase/decrease in height to accommodate the quantity of text in the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will need an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?








  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Susan
 
Posts: n/a
Default Row Expansion

Otto,

I am trying to enter the macro from your message below, but I'm getting an
error message with this line of the macro: If Intersect(Target, Range
(Range_1))_Then. I named my range Range_1.

Susan

"Otto Moehrbach" wrote:

Susan
I don't know what you have in your file for it to act this way. If the
source cell is formatted for Wrap and you copy it and paste it into any
cell, that cell (the destination cell) will be formatted for Wrap regardless
of its format before the paste. You say that is not happening for you.
Yes, there is a way to automatically format a cell immediately after you
paste into it. You would need a Worksheet_Change event macro. Such a macro
fires (executes) whenever the content of any cell in the entire sheet
changes. You would need to write the code of the macro to take the action
you want (format to wrap) whenever any cell within your range changes. I'm
sure you don't want this macro to take the Wrap action when any cell in the
entire sheet changes. Can you specify the range in which you want this
action to occur? Like "Anywhere in Column A from row 3 to row 150". Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever range you
come up with, you need to name it. Say "TheRange". The following macro
will change the format of the cell to Wrap when the changed cell is within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It MUST be
placed in the sheet module for the sheet in which you want this to happen.
To access that module, right-click on the sheet tab of your sheet. From the
menu that pops up select View Code. Paste this macro into that module.
Click on the "X" in the top right corner of the module to return to your
sheet. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Otto,

The source cell from where I am transferring data is already set to word
wrap. I need a macro that when the data is transferred to the
destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it into a cell
that is formatted for Wrap, the format of the source cell comes along
with
the paste and you lose the Wrap formatting. You can manually set the
format
of that cell back to Wrap or you can run the following macro with that
cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into which
you
will be pasting the data. Something like "any cell in Column A" or "any
cell in Column A from this row to that row". Or any other column or
groups
of columns and groups of rows. If you can nail down the range into which
you will be pasting, then when you paste into any cell in that range, the
format will be changed to Wrap. Post back if you think something like
this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is selected
but
the row does not increase in height when text is transferred. If I do
a
column expansion, I'm afraid my spreadsheet will exceed my margins. Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will automatically
increase/decrease in height to accommodate the quantity of text in the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will need an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?









  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach
 
Posts: n/a
Default Row Expansion

Susan
You need to put the range name in quotes, "Range_1". HTH Otto
"Susan" wrote in message
...
Otto,

I am trying to enter the macro from your message below, but I'm getting an
error message with this line of the macro: If Intersect(Target, Range
(Range_1))_Then. I named my range Range_1.

Susan

"Otto Moehrbach" wrote:

Susan
I don't know what you have in your file for it to act this way. If
the
source cell is formatted for Wrap and you copy it and paste it into any
cell, that cell (the destination cell) will be formatted for Wrap
regardless
of its format before the paste. You say that is not happening for you.
Yes, there is a way to automatically format a cell immediately after
you
paste into it. You would need a Worksheet_Change event macro. Such a
macro
fires (executes) whenever the content of any cell in the entire sheet
changes. You would need to write the code of the macro to take the
action
you want (format to wrap) whenever any cell within your range changes.
I'm
sure you don't want this macro to take the Wrap action when any cell in
the
entire sheet changes. Can you specify the range in which you want this
action to occur? Like "Anywhere in Column A from row 3 to row 150". Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever range you
come up with, you need to name it. Say "TheRange". The following macro
will change the format of the cell to Wrap when the changed cell is
within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It MUST
be
placed in the sheet module for the sheet in which you want this to
happen.
To access that module, right-click on the sheet tab of your sheet. From
the
menu that pops up select View Code. Paste this macro into that module.
Click on the "X" in the top right corner of the module to return to your
sheet. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Otto,

The source cell from where I am transferring data is already set to
word
wrap. I need a macro that when the data is transferred to the
destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it into a
cell
that is formatted for Wrap, the format of the source cell comes along
with
the paste and you lose the Wrap formatting. You can manually set the
format
of that cell back to Wrap or you can run the following macro with that
cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into which
you
will be pasting the data. Something like "any cell in Column A" or
"any
cell in Column A from this row to that row". Or any other column or
groups
of columns and groups of rows. If you can nail down the range into
which
you will be pasting, then when you paste into any cell in that range,
the
format will be changed to Wrap. Post back if you think something like
this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is
selected
but
the row does not increase in height when text is transferred. If I
do
a
column expansion, I'm afraid my spreadsheet will exceed my margins.
Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will
automatically
increase/decrease in height to accommodate the quantity of text in
the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will need
an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?











  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Susan
 
Posts: n/a
Default Row Expansion

Otto,

I input the macro as follows:

Sub SetWrap()
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub

My rows are still not widening when the info is transferred. I looked back
at your directions and noticed that you referred to pasting. The data that I
am transferring to the destination cell is getting there from code inserted
into visual basic. Would that affect the macro you are giving me from
working?

Susan

"Otto Moehrbach" wrote:

Susan
You need to put the range name in quotes, "Range_1". HTH Otto
"Susan" wrote in message
...
Otto,

I am trying to enter the macro from your message below, but I'm getting an
error message with this line of the macro: If Intersect(Target, Range
(Range_1))_Then. I named my range Range_1.

Susan

"Otto Moehrbach" wrote:

Susan
I don't know what you have in your file for it to act this way. If
the
source cell is formatted for Wrap and you copy it and paste it into any
cell, that cell (the destination cell) will be formatted for Wrap
regardless
of its format before the paste. You say that is not happening for you.
Yes, there is a way to automatically format a cell immediately after
you
paste into it. You would need a Worksheet_Change event macro. Such a
macro
fires (executes) whenever the content of any cell in the entire sheet
changes. You would need to write the code of the macro to take the
action
you want (format to wrap) whenever any cell within your range changes.
I'm
sure you don't want this macro to take the Wrap action when any cell in
the
entire sheet changes. Can you specify the range in which you want this
action to occur? Like "Anywhere in Column A from row 3 to row 150". Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever range you
come up with, you need to name it. Say "TheRange". The following macro
will change the format of the cell to Wrap when the changed cell is
within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It MUST
be
placed in the sheet module for the sheet in which you want this to
happen.
To access that module, right-click on the sheet tab of your sheet. From
the
menu that pops up select View Code. Paste this macro into that module.
Click on the "X" in the top right corner of the module to return to your
sheet. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Otto,

The source cell from where I am transferring data is already set to
word
wrap. I need a macro that when the data is transferred to the
destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it into a
cell
that is formatted for Wrap, the format of the source cell comes along
with
the paste and you lose the Wrap formatting. You can manually set the
format
of that cell back to Wrap or you can run the following macro with that
cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into which
you
will be pasting the data. Something like "any cell in Column A" or
"any
cell in Column A from this row to that row". Or any other column or
groups
of columns and groups of rows. If you can nail down the range into
which
you will be pasting, then when you paste into any cell in that range,
the
format will be changed to Wrap. Post back if you think something like
this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is
selected
but
the row does not increase in height when text is transferred. If I
do
a
column expansion, I'm afraid my spreadsheet will exceed my margins.
Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will
automatically
increase/decrease in height to accommodate the quantity of text in
the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will need
an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info is
transferred
to it from another sheet?












  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach
 
Posts: n/a
Default Row Expansion

Susan
I messed you up. I told you I was giving you an event macro then I gave
you standard macro.
Here is the macro you should be using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub
And this will work regardless of who puts data into the Range_1 range, you
or the code. Sorry for the mix-up. Otto
"Susan" wrote in message
...
Otto,

I input the macro as follows:

Sub SetWrap()
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub

My rows are still not widening when the info is transferred. I looked
back
at your directions and noticed that you referred to pasting. The data
that I
am transferring to the destination cell is getting there from code
inserted
into visual basic. Would that affect the macro you are giving me from
working?

Susan

"Otto Moehrbach" wrote:

Susan
You need to put the range name in quotes, "Range_1". HTH Otto
"Susan" wrote in message
...
Otto,

I am trying to enter the macro from your message below, but I'm getting
an
error message with this line of the macro: If Intersect(Target, Range
(Range_1))_Then. I named my range Range_1.

Susan

"Otto Moehrbach" wrote:

Susan
I don't know what you have in your file for it to act this way.
If
the
source cell is formatted for Wrap and you copy it and paste it into
any
cell, that cell (the destination cell) will be formatted for Wrap
regardless
of its format before the paste. You say that is not happening for
you.
Yes, there is a way to automatically format a cell immediately
after
you
paste into it. You would need a Worksheet_Change event macro. Such a
macro
fires (executes) whenever the content of any cell in the entire sheet
changes. You would need to write the code of the macro to take the
action
you want (format to wrap) whenever any cell within your range changes.
I'm
sure you don't want this macro to take the Wrap action when any cell
in
the
entire sheet changes. Can you specify the range in which you want
this
action to occur? Like "Anywhere in Column A from row 3 to row 150".
Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever range
you
come up with, you need to name it. Say "TheRange". The following
macro
will change the format of the cell to Wrap when the changed cell is
within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It
MUST
be
placed in the sheet module for the sheet in which you want this to
happen.
To access that module, right-click on the sheet tab of your sheet.
From
the
menu that pops up select View Code. Paste this macro into that
module.
Click on the "X" in the top right corner of the module to return to
your
sheet. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Otto,

The source cell from where I am transferring data is already set to
word
wrap. I need a macro that when the data is transferred to the
destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having
to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it into a
cell
that is formatted for Wrap, the format of the source cell comes
along
with
the paste and you lose the Wrap formatting. You can manually set
the
format
of that cell back to Wrap or you can run the following macro with
that
cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into
which
you
will be pasting the data. Something like "any cell in Column A"
or
"any
cell in Column A from this row to that row". Or any other column
or
groups
of columns and groups of rows. If you can nail down the range into
which
you will be pasting, then when you paste into any cell in that
range,
the
format will be changed to Wrap. Post back if you think something
like
this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is
selected
but
the row does not increase in height when text is transferred. If
I
do
a
column expansion, I'm afraid my spreadsheet will exceed my
margins.
Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will
automatically
increase/decrease in height to accommodate the quantity of text
in
the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will
need
an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info
is
transferred
to it from another sheet?
















  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Susan
 
Posts: n/a
Default Row Expansion

Otto,

The macro didn't work again. I typed it in just as you had it, but no luck.
I think I'm going to give up on this. Thank you for your help and patience.

Susan Spencer

"Otto Moehrbach" wrote:

Susan
I messed you up. I told you I was giving you an event macro then I gave
you standard macro.
Here is the macro you should be using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub
And this will work regardless of who puts data into the Range_1 range, you
or the code. Sorry for the mix-up. Otto
"Susan" wrote in message
...
Otto,

I input the macro as follows:

Sub SetWrap()
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub

My rows are still not widening when the info is transferred. I looked
back
at your directions and noticed that you referred to pasting. The data
that I
am transferring to the destination cell is getting there from code
inserted
into visual basic. Would that affect the macro you are giving me from
working?

Susan

"Otto Moehrbach" wrote:

Susan
You need to put the range name in quotes, "Range_1". HTH Otto
"Susan" wrote in message
...
Otto,

I am trying to enter the macro from your message below, but I'm getting
an
error message with this line of the macro: If Intersect(Target, Range
(Range_1))_Then. I named my range Range_1.

Susan

"Otto Moehrbach" wrote:

Susan
I don't know what you have in your file for it to act this way.
If
the
source cell is formatted for Wrap and you copy it and paste it into
any
cell, that cell (the destination cell) will be formatted for Wrap
regardless
of its format before the paste. You say that is not happening for
you.
Yes, there is a way to automatically format a cell immediately
after
you
paste into it. You would need a Worksheet_Change event macro. Such a
macro
fires (executes) whenever the content of any cell in the entire sheet
changes. You would need to write the code of the macro to take the
action
you want (format to wrap) whenever any cell within your range changes.
I'm
sure you don't want this macro to take the Wrap action when any cell
in
the
entire sheet changes. Can you specify the range in which you want
this
action to occur? Like "Anywhere in Column A from row 3 to row 150".
Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever range
you
come up with, you need to name it. Say "TheRange". The following
macro
will change the format of the cell to Wrap when the changed cell is
within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It
MUST
be
placed in the sheet module for the sheet in which you want this to
happen.
To access that module, right-click on the sheet tab of your sheet.
From
the
menu that pops up select View Code. Paste this macro into that
module.
Click on the "X" in the top right corner of the module to return to
your
sheet. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Otto,

The source cell from where I am transferring data is already set to
word
wrap. I need a macro that when the data is transferred to the
destination
cell the row that it is being transferred to will increase in height
automatically to accomodate viewing the entire cell without having
to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it into a
cell
that is formatted for Wrap, the format of the source cell comes
along
with
the paste and you lose the Wrap formatting. You can manually set
the
format
of that cell back to Wrap or you can run the following macro with
that
cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into
which
you
will be pasting the data. Something like "any cell in Column A"
or
"any
cell in Column A from this row to that row". Or any other column
or
groups
of columns and groups of rows. If you can nail down the range into
which
you will be pasting, then when you paste into any cell in that
range,
the
format will be changed to Wrap. Post back if you think something
like
this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is
selected
but
the row does not increase in height when text is transferred. If
I
do
a
column expansion, I'm afraid my spreadsheet will exceed my
margins.
Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will
automatically
increase/decrease in height to accommodate the quantity of text
in
the
cell.
Is that what you wanted?
If you want the column width to change automatically, you will
need
an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when info
is
transferred
to it from another sheet?















  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Otto Moehrbach
 
Posts: n/a
Default Row Expansion

Susan
Don't give up. This stuff is too easy to just give up on it. What you
want to do is very common and is done by lots of people virtually all the
time. If you wish, send me your file or a sample of it. Fake the actual
data if you feel it is proprietary. I want just the layout. I don't need
your code that puts data into one of the Range_1 cells if you don't want to
send it. I can write that myself.
My email address is . Remove the "nop" from this
address. Otto
"Susan" wrote in message
...
Otto,

The macro didn't work again. I typed it in just as you had it, but no
luck.
I think I'm going to give up on this. Thank you for your help and
patience.

Susan Spencer

"Otto Moehrbach" wrote:

Susan
I messed you up. I told you I was giving you an event macro then I
gave
you standard macro.
Here is the macro you should be using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub
And this will work regardless of who puts data into the Range_1 range,
you
or the code. Sorry for the mix-up. Otto
"Susan" wrote in message
...
Otto,

I input the macro as follows:

Sub SetWrap()
If Target = "" Then Exit Sub
If Intersect(Target, Range("Range_1")) Then
Target.WrapText = True
End Sub

My rows are still not widening when the info is transferred. I looked
back
at your directions and noticed that you referred to pasting. The data
that I
am transferring to the destination cell is getting there from code
inserted
into visual basic. Would that affect the macro you are giving me from
working?

Susan

"Otto Moehrbach" wrote:

Susan
You need to put the range name in quotes, "Range_1". HTH Otto
"Susan" wrote in message
...
Otto,

I am trying to enter the macro from your message below, but I'm
getting
an
error message with this line of the macro: If Intersect(Target,
Range
(Range_1))_Then. I named my range Range_1.

Susan

"Otto Moehrbach" wrote:

Susan
I don't know what you have in your file for it to act this way.
If
the
source cell is formatted for Wrap and you copy it and paste it into
any
cell, that cell (the destination cell) will be formatted for Wrap
regardless
of its format before the paste. You say that is not happening for
you.
Yes, there is a way to automatically format a cell immediately
after
you
paste into it. You would need a Worksheet_Change event macro.
Such a
macro
fires (executes) whenever the content of any cell in the entire
sheet
changes. You would need to write the code of the macro to take the
action
you want (format to wrap) whenever any cell within your range
changes.
I'm
sure you don't want this macro to take the Wrap action when any
cell
in
the
entire sheet changes. Can you specify the range in which you want
this
action to occur? Like "Anywhere in Column A from row 3 to row
150".
Or
"Anywhere in Columns C:K from rows 23 to 432. Or etc. Whatever
range
you
come up with, you need to name it. Say "TheRange". The following
macro
will change the format of the cell to Wrap when the changed cell is
within
TheRange.
Sub SetWrap()
If Target="" Then Exit Sub
If Intersect(Target, Range("TheRange")) Then _
Target.WrapText = True
End Sub

Note that such a macro must NOT be placed in a standard module. It
MUST
be
placed in the sheet module for the sheet in which you want this to
happen.
To access that module, right-click on the sheet tab of your sheet.
From
the
menu that pops up select View Code. Paste this macro into that
module.
Click on the "X" in the top right corner of the module to return to
your
sheet. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Otto,

The source cell from where I am transferring data is already set
to
word
wrap. I need a macro that when the data is transferred to the
destination
cell the row that it is being transferred to will increase in
height
automatically to accomodate viewing the entire cell without
having
to
manually adjust the height. Is there a way to do that?

Thanks again,
Susan Spencer

"Otto Moehrbach" wrote:

Susan
Your right. When you copy data from a cell and paste it
into a
cell
that is formatted for Wrap, the format of the source cell comes
along
with
the paste and you lose the Wrap formatting. You can manually
set
the
format
of that cell back to Wrap or you can run the following macro
with
that
cell
selected:
Sub SetWrap()
Selection.WrapText = True
End Sub

There is a way to automate this also, if you know the range into
which
you
will be pasting the data. Something like "any cell in Column
A"
or
"any
cell in Column A from this row to that row". Or any other
column
or
groups
of columns and groups of rows. If you can nail down the range
into
which
you will be pasting, then when you paste into any cell in that
range,
the
format will be changed to Wrap. Post back if you think
something
like
this
will help you. HTH Otto
"Susan" wrote in message
...
I checked my cells formatting and the text wrapping option is
selected
but
the row does not increase in height when text is transferred.
If
I
do
a
column expansion, I'm afraid my spreadsheet will exceed my
margins.
Is
there
a macro for expanding the height of the cells?

Thanks for your reply.

Susan Spencer

"Otto Moehrbach" wrote:

If you set the destination cell to Wrap, the cell will
automatically
increase/decrease in height to accommodate the quantity of
text
in
the
cell.
Is that what you wanted?
If you want the column width to change automatically, you
will
need
an
event
macro. Please post back if you need more. HTH Otto
"Susan" wrote in message
...
Is there anyway to set a row to automatically expand when
info
is
transferred
to it from another sheet?

















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatic Chart Expansion Problem Mike Fox Charts and Charting in Excel 1 January 2nd 06 02:16 PM
Do Pivot Tables have an automatic data range expansion? David.c.h Excel Discussion (Misc queries) 1 March 26th 05 01:55 AM
number expansion and contraction tom donino Excel Worksheet Functions 2 December 23rd 04 09:11 PM


All times are GMT +1. The time now is 04:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"