Keep Width while copy

[clara]

Hi all,

I have a need to copy a range from a worksheet to a doc. many cells contain
values in the following format:
TEXT [99]

the TEXT is for display and [99] is for other purposes.Between them are
spaces.
I don't like the [99] part appear, so I adjust the cell's width to hide it
in worksheet. When I copy the range to a doc, I still do not want the [99]
appear, so I want to keep the same column's width. Of course, I would finish
copy in VBA, so could you give me some clues?

Clara

 

[Jean-Guy Marcil]

Hi all,

I have a need to copy a range from a worksheet to a doc. many cells contain
values in the following format:
TEXT [99]

the TEXT is for display and [99] is for other purposes.Between them are
spaces.
I don't like the [99] part appear, so I adjust the cell's width to hide it
in worksheet. When I copy the range to a doc, I still do not want the [99]
appear, so I want to keep the same column's width. Of course, I would finish
copy in VBA, so could you give me some clues?

As you have probably found out already, you cannot just reduce the width of
the pasted colum to hide the [Text] parts because Word will automatically
wrap the text that does not fit in the cell width, whereas in Excel you can
prevent that.
By the way, this is not an efficient way of working in Excel. You should
have the text in one column, then the [text] part in another column and then
hide that column. Much more efficient than mucking around with column widths
and adding spaces...

In Word you will have to hide the [Text] parts.
For now, you can use the following code to hide the [Text] part in the
pasted column. Just place the cursor anywhere in the column with the [Text]
in that pasted table.

Dim colTarget As Column
Dim rngCell As Range
Dim i As Long

If Not Selection.Information(wdWithInTable) Then
MsgBox "Place cursor in table."
Exit Sub
End If

Set colTarget = Selection.Columns(1)

With colTarget
For i = 1 To .Cells.Count
Set rngCell = .Cells(i).Range
With rngCell.Find
.Text = "\[*\]"
.MatchWildcards = True
If .Execute Then
.Parent.Font.Hidden = True
End If
End With
Next
End With

 

[Reitanos]

An alternate option is to use Edit/Paste Special and choose one of the
Picture formats.
The two downsides to this option are
1) It's not very pretty and cannot be reformatted in Word
2) It's not exact because of font issues (double-check the display of
the image to be sure)

Another option is to adjust the column width and increase the right
margin in each cell so that the text is allowed to flow beyond the
border in much the same way as it happens in Excel.

Hi all,

I have a need to copy a range from a worksheet to a doc. many cells contain
values in the following format:
TEXT [99]

the TEXT is for display and [99] is for other purposes.Between them are
spaces.
I don't like the [99] part appear, so I adjust the cell's width to hide it
in worksheet. When I copy the range to a doc, I still do not want the [99]
appear, so I want to keep the same column's width. Of course, I would finish
copy in VBA, so could you give me some clues?

As you have probably found out already, you cannot just reduce the width of
the pasted colum to hide the [Text] parts because Word will automatically
wrap the text that does not fit in the cell width, whereas in Excel you can
prevent that.
By the way, this is not an efficient way of working in Excel. You should
have the text in one column, then the [text] part in another column and then
hide that column. Much more efficient than mucking around with column widths
and adding spaces...

In Word you will have to hide the [Text] parts.
For now, you can use the following code to hide the [Text] part in the
pasted column. Just place the cursor anywhere in the column with the [Text]
in that pasted table.

Dim colTarget As Column
Dim rngCell As Range
Dim i As Long

If Not Selection.Information(wdWithInTable) Then
MsgBox "Place cursor in table."
Exit Sub
End If

Set colTarget = Selection.Columns(1)

With colTarget
For i = 1 To .Cells.Count
Set rngCell = .Cells(i).Range
With rngCell.Find
.Text = "\[*\]"
.MatchWildcards = True
If .Execute Then
.Parent.Font.Hidden = True
End If
End With
Next
End With

 

[clara]

Hi Reitanos,

Your suggestion is very helpful. I can find Edit/Paste Special, but I can
not find the Picture formats.

Thank you very much!
--
thank you so much for your help

An alternate option is to use Edit/Paste Special and choose one of the
Picture formats.
The two downsides to this option are
1) It's not very pretty and cannot be reformatted in Word
2) It's not exact because of font issues (double-check the display of
the image to be sure)

Another option is to adjust the column width and increase the right
margin in each cell so that the text is allowed to flow beyond the
border in much the same way as it happens in Excel.

Hi all,

I have a need to copy a range from a worksheet to a doc. many cells contain
values in the following format:
TEXT [99]

the TEXT is for display and [99] is for other purposes.Between them are
spaces.
I don't like the [99] part appear, so I adjust the cell's width to hide it
in worksheet. When I copy the range to a doc, I still do not want the [99]
appear, so I want to keep the same column's width. Of course, I would finish
copy in VBA, so could you give me some clues?

As you have probably found out already, you cannot just reduce the width of
the pasted colum to hide the [Text] parts because Word will automatically
wrap the text that does not fit in the cell width, whereas in Excel you can
prevent that.
By the way, this is not an efficient way of working in Excel. You should
have the text in one column, then the [text] part in another column and then
hide that column. Much more efficient than mucking around with column widths
and adding spaces...

In Word you will have to hide the [Text] parts.
For now, you can use the following code to hide the [Text] part in the
pasted column. Just place the cursor anywhere in the column with the [Text]
in that pasted table.

Dim colTarget As Column
Dim rngCell As Range
Dim i As Long

If Not Selection.Information(wdWithInTable) Then
MsgBox "Place cursor in table."
Exit Sub
End If

Set colTarget = Selection.Columns(1)

With colTarget
For i = 1 To .Cells.Count
Set rngCell = .Cells(i).Range
With rngCell.Find
.Text = "\[*\]"
.MatchWildcards = True
If .Execute Then
.Parent.Font.Hidden = True
End If
End With
Next
End With