Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > PasteSpecial problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default PasteSpecial problem
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end). It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default PasteSpecial problem
Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end).
It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW
TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default PasteSpecial problem
And if you use Excel 2000 you must use the number 8 (Bug in 2000)
But 8 will work in every version

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Chip Pearson" wrote in message ...
Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end).
It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW
TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default PasteSpecial problem
Ron - I am using XL2000, but I don't understand what you mean by using the
number 8. Can you explain further?
--
Jim T


"Ron de Bruin" wrote:

And if you use Excel 2000 you must use the number 8 (Bug in 2000)
But 8 will work in every version

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Chip Pearson" wrote in message ...
Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end).
It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW
TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default PasteSpecial problem
Hi Jim

Use the value
PasteSpecial Paste:=8

Instead of the name
PasteSpecial Paste:=xlPasteColumnWidths

This is a bug in Excel 2000 and the workaround is to use the value

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jim Tibbetts" wrote in message ...
Ron - I am using XL2000, but I don't understand what you mean by using the
number 8. Can you explain further?
--
Jim T


"Ron de Bruin" wrote:

And if you use Excel 2000 you must use the number 8 (Bug in 2000)
But 8 will work in every version

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Chip Pearson" wrote in message ...
Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end).
It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW
TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default PasteSpecial problem
If I remember correct xlPasteValidation have the same problem in Excel 2000
Use 6 then

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi Jim

Use the value PasteSpecial Paste:=8

Instead of the name
PasteSpecial Paste:=xlPasteColumnWidths

This is a bug in Excel 2000 and the workaround is to use the value

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jim Tibbetts" wrote in message ...
Ron - I am using XL2000, but I don't understand what you mean by using the number 8. Can you explain further?
--
Jim T


"Ron de Bruin" wrote:

And if you use Excel 2000 you must use the number 8 (Bug in 2000)
But 8 will work in every version

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Chip Pearson" wrote in message ...
Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end). It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default PasteSpecial problem
Hi Chip. Thanks for the quick reply. The code that was in my macro was from
one that I had recorded. I did make the change you suggested, but the macro
still hangs on that line. Any other ideas?
--
Jim T


"Chip Pearson" wrote:

Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the end).
It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW
TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) = 0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default PasteSpecial problem
The code worked fine for me. What do you actually mean by "hangs on that
line"? You might try using real range reference rather than Selection. It
(almost) never necessary to Select anything in VBA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Hi Chip. Thanks for the quick reply. The code that was in my macro was
from
one that I had recorded. I did make the change you suggested, but the
macro
still hangs on that line. Any other ideas?
--
Jim T


"Chip Pearson" wrote:

Jim,

xlColumnWidths
should be
xlPasteColumnWidths


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Jim Tibbetts" wrote in message
...
Can anyone tell me why this macro will not PasteSpecial the Columnwidth
attribute(the one I marked with a double asterisk, 4th line from the
end).
It
handles the other PasteSpecials with no problem.

Sub ProcessWeeklyData()

' Moves last week's NEW TOTAL to PREV TTL and copies THIS WEEK to NEW
TOTAL.
Range("WeeklyInfo!D10:D59").Select
Selection.Copy
Range("WeeklyInfo!B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!H10:H59").Select
Selection.Copy
Range("WeeklyInfo!C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Range("WeeklyInfo!A1:F46").Select
Selection.Copy
Range("WeeklyInfo!A1").Select
' Copies updated weekly information to a new page.
For Each Worksheet In Worksheets
If Application.WorksheetFunction.CountA(Worksheet.Use dRange.Cells) =
0
Then
Worksheet.Select
Exit For
End If
Next
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
** Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.DisplayGridlines = False

End Sub

Thanks for any help,
--
Jim T





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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
Problem in PasteSpecial function ojas_maru Excel Programming 2 June 3rd 05 01:20 PM
selection.pastespecial problem rleonard[_2_] Excel Programming 1 May 31st 04 10:56 AM
FindNext problem when using PasteSpecial Glyn Baker Excel Programming 1 December 6th 03 08:40 PM
PasteSpecial problem Dave Peterson[_3_] Excel Programming 2 November 6th 03 02:45 AM
PasteSpecial problem Jim Rech Excel Programming 0 November 4th 03 04:47 PM


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

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement