Macro to paste Column Width doesn't work? Help!

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
Every time I record a macro to copy a range and then paste the column widths to another range it doesn't work. Here's the code I get?

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Is there an alternative way?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Yep, this is a good start:

Sub CopywithRowAndColwidths()
Dim lCount As Long
Dim rngCopy As Range
Dim rngPaste As Range
On Error Resume Next
Set rngCopy = Application.InputBox("Select the range to copy", , , , , , , :cool:
If Not rngCopy Is Nothing Then
Set rngPaste = Application.InputBox("Select the cell to paste to", , , , , , , :cool:
On Error GoTo 0
If Not rngPaste Is Nothing Then
rngCopy.Copy
rngPaste.Cells(1, 1).PasteSpecial xlPasteAll
For lCount = 1 To rngCopy.Columns.Count
rngPaste.Cells(1, lCount).ColumnWidth = _
rngCopy.Columns(lCount).ColumnWidth
Next lCount
For lCount = 1 To rngCopy.Rows.Count
rngPaste.Cells(lCount, 1).RowHeight = _
rngCopy.Rows(lCount).RowHeight
Next lCount
Application.CutCopyMode = False
End If
End If
End Sub


Mix your code in here, play with the xlpasteall, change the input boxes to cell ranges, etc.....

Hope that helps. Cheers,

Nate
 
Upvote 0
Also, change the smileys to "8)" (no quotes). In the time I just typed this I should have put "type:=" in front of it! :(


Cheers,

Nate
This message was edited by NateO on 2002-03-13 11:25
 
Upvote 0
NateO,

Thanks! This works but I don't want to what is in the rngCopy, I just want to paste the column widths of rngCopy and not what is in them. How do I modify this to do that?
 
Upvote 0
Jes need to remove one line, which I've taken the liberty of doing below:

Sub CopywithRowAndColwidths2()
Dim lCount As Long
Dim rngCopy As Range
Dim rngPaste As Range
On Error Resume Next
Set rngCopy = Application.InputBox(prompt:="Select the range to copy", Type:=8)
If Not rngCopy Is Nothing Then
Set rngPaste = Application.InputBox(prompt:="Select the cell to paste to", Type:=8)
On Error GoTo 0
If Not rngPaste Is Nothing Then
rngCopy.Copy
For lCount = 1 To rngCopy.Columns.Count
rngPaste.Cells(1, lCount).ColumnWidth = _
rngCopy.Columns(lCount).ColumnWidth
Next lCount
For lCount = 1 To rngCopy.Rows.Count
rngPaste.Cells(lCount, 1).RowHeight = _
rngCopy.Rows(lCount).RowHeight
Next lCount
Application.CutCopyMode = False
End If
End If
End Sub


Hope this helps. Cheers,

Nate
 
Upvote 0
On 2002-03-13 10:56, Cosmos75 wrote:
Every time I record a macro to copy a range and then paste the column widths to another range it doesn't work. Here's the code I get?

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Is there an alternative way?

change code to;

Selection.PasteSpecial Paste:=8, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


or

something like;

Const xlColumnWidths = 8

Sub PasteSpec_XlColWdth()

Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub


Ivan
This message was edited by Ivan F Moala on 2002-03-14 23:26
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top