|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
PasteSpecial problem
Got it. Thanks for the help Ron.
-- Jim T "Ron de Bruin" wrote: 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 |
| All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com