|
|
|
Author |
Message |
IkonTech
|
Posted: Sun Jan 20 22:41:00 CST 2008 |
Top |
Excel Programming >> PasteSpecial issue
I use the code to copy data from a temporary worksheet into a production
worksheet and it works fine with the "ActiveSheet.Paste" line near the
bottom. The problem is that I need to use PasteSpecial instead . I tried
using " ActiveSheet.PasteSpecial Paste:=xlPasteValues" but I get an
"Application-defined or object-defined error"
Public Sub CopyPasteToday()
Dim Targetbook As Workbook
'The workbook that will receive the data
Set Targetbook = ActiveWorkbook
Module3.Disable_Events
Range("A" & ActiveCell.Row).Select
ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
ActiveCell.Offset(2, 0).EntireRow.Columns("A").Select
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Workbooks.Open Filename:="P:\NoShow\tempnoshow.xls"
Sheets("Sheet1").Select
With Workbooks("TempNoShow.xls").Worksheets("Sheet1")
Columns("E:E").Select
Selection.NumberFormat = "mm/dd/yyyy"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2:E35").Select
Selection.Copy
Windows("No_Show_2008.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste
End With
Windows("TempNoShow.xls").Close
ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
ActiveWorkbook.Activate
Application.WindowState = xlMaximized
Module3.Enable_Events
End Sub
Excel512
|
|
|
|
|
FSt1
|
Posted: Sun Jan 20 22:41:00 CST 2008 |
Top |
Excel Programming >> PasteSpecial issue
hi
you could change Activesheet.paste to
selection.pastespecial xlpastevalues
but what i would do is instead of these lines.....
ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste
I would change to.....
Range("C65000").end(xlup).offset(1,-1). _
Pastespecial xlpastevalues
one line...wrapped. this would eliminate the need for selecting.
Regards
FSt1
> I use the code to copy data from a temporary worksheet into a production
> worksheet and it works fine with the "ActiveSheet.Paste" line near the
> bottom. The problem is that I need to use PasteSpecial instead . I tried
> using " ActiveSheet.PasteSpecial Paste:=xlPasteValues" but I get an
> "Application-defined or object-defined error"
>
>
> Public Sub CopyPasteToday()
>
> Dim Targetbook As Workbook
>
> 'The workbook that will receive the data
> Set Targetbook = ActiveWorkbook
>
> Module3.Disable_Events
>
> Range("A" & ActiveCell.Row).Select
>
> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
> ActiveCell.Offset(2, 0).EntireRow.Columns("A").Select
>
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
>
> Workbooks.Open Filename:="P:\NoShow\tempnoshow.xls"
> Sheets("Sheet1").Select
>
> With Workbooks("TempNoShow.xls").Worksheets("Sheet1")
> Columns("E:E").Select
> Selection.NumberFormat = "mm/dd/yyyy"
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> ActiveWindow.ScrollColumn = 4
> ActiveWindow.ScrollColumn = 3
> ActiveWindow.ScrollColumn = 2
> ActiveWindow.ScrollColumn = 1
> Range("A2:E35").Select
> Selection.Copy
> Windows("No_Show_2008.xls").Activate
> ActiveWindow.WindowState = xlMaximized
> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
>
>
> ActiveSheet.Paste
>
>
> End With
>
> Windows("TempNoShow.xls").Close
>
> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
> ActiveWorkbook.Activate
>
> Application.WindowState = xlMaximized
>
> Module3.Enable_Events
>
> End Sub
>
>
|
|
|
|
|
Patrick
|
Posted: Sun Jan 20 23:11:02 CST 2008 |
Top |
Excel Programming >> PasteSpecial issue
Thank you, that did the trick.
> hi
> you could change Activesheet.paste to
> selection.pastespecial xlpastevalues
> but what i would do is instead of these lines.....
>
> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
> ActiveSheet.Paste
>
> I would change to.....
>
> Range("C65000").end(xlup).offset(1,-1). _
> Pastespecial xlpastevalues
>
> one line...wrapped. this would eliminate the need for selecting.
>
> Regards
> FSt1
>
>
>> I use the code to copy data from a temporary worksheet into a production
>> worksheet and it works fine with the "ActiveSheet.Paste" line near the
>> bottom. The problem is that I need to use PasteSpecial instead . I tried
>> using " ActiveSheet.PasteSpecial Paste:=xlPasteValues" but I get an
>> "Application-defined or object-defined error"
>>
>>
>> Public Sub CopyPasteToday()
>>
>> Dim Targetbook As Workbook
>>
>> 'The workbook that will receive the data
>> Set Targetbook = ActiveWorkbook
>>
>> Module3.Disable_Events
>>
>> Range("A" & ActiveCell.Row).Select
>>
>> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
>> ActiveCell.Offset(2, 0).EntireRow.Columns("A").Select
>>
>> Application.DisplayAlerts = False
>> Application.ScreenUpdating = False
>>
>> Workbooks.Open Filename:="P:\NoShow\tempnoshow.xls"
>> Sheets("Sheet1").Select
>>
>> With Workbooks("TempNoShow.xls").Worksheets("Sheet1")
>> Columns("E:E").Select
>> Selection.NumberFormat = "mm/dd/yyyy"
>> With Selection
>> .HorizontalAlignment = xlCenter
>> .VerticalAlignment = xlCenter
>> .WrapText = False
>> .Orientation = 0
>> .AddIndent = False
>> .IndentLevel = 0
>> .ShrinkToFit = False
>> .ReadingOrder = xlContext
>> .MergeCells = False
>> End With
>> ActiveWindow.ScrollColumn = 4
>> ActiveWindow.ScrollColumn = 3
>> ActiveWindow.ScrollColumn = 2
>> ActiveWindow.ScrollColumn = 1
>> Range("A2:E35").Select
>> Selection.Copy
>> Windows("No_Show_2008.xls").Activate
>> ActiveWindow.WindowState = xlMaximized
>> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
>>
>>
>> ActiveSheet.Paste
>>
>>
>> End With
>>
>> Windows("TempNoShow.xls").Close
>>
>> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
>> ActiveWorkbook.Activate
>>
>> Application.WindowState = xlMaximized
>>
>> Module3.Enable_Events
>>
>> End Sub
>>
>>
|
|
|
|
|
FSt1
|
Posted: Sun Jan 20 23:20:00 CST 2008 |
Top |
Excel Programming >> PasteSpecial issue
you're welcome
Regards
FSt1
> Thank you, that did the trick.
>
> > hi
> > you could change Activesheet.paste to
> > selection.pastespecial xlpastevalues
> > but what i would do is instead of these lines.....
> >
> > ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
> > ActiveSheet.Paste
> >
> > I would change to.....
> >
> > Range("C65000").end(xlup).offset(1,-1). _
> > Pastespecial xlpastevalues
> >
> > one line...wrapped. this would eliminate the need for selecting.
> >
> > Regards
> > FSt1
> >
> >
> >> I use the code to copy data from a temporary worksheet into a production
> >> worksheet and it works fine with the "ActiveSheet.Paste" line near the
> >> bottom. The problem is that I need to use PasteSpecial instead . I tried
> >> using " ActiveSheet.PasteSpecial Paste:=xlPasteValues" but I get an
> >> "Application-defined or object-defined error"
> >>
> >>
> >> Public Sub CopyPasteToday()
> >>
> >> Dim Targetbook As Workbook
> >>
> >> 'The workbook that will receive the data
> >> Set Targetbook = ActiveWorkbook
> >>
> >> Module3.Disable_Events
> >>
> >> Range("A" & ActiveCell.Row).Select
> >>
> >> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
> >> ActiveCell.Offset(2, 0).EntireRow.Columns("A").Select
> >>
> >> Application.DisplayAlerts = False
> >> Application.ScreenUpdating = False
> >>
> >> Workbooks.Open Filename:="P:\NoShow\tempnoshow.xls"
> >> Sheets("Sheet1").Select
> >>
> >> With Workbooks("TempNoShow.xls").Worksheets("Sheet1")
> >> Columns("E:E").Select
> >> Selection.NumberFormat = "mm/dd/yyyy"
> >> With Selection
> >> .HorizontalAlignment = xlCenter
> >> .VerticalAlignment = xlCenter
> >> .WrapText = False
> >> .Orientation = 0
> >> .AddIndent = False
> >> .IndentLevel = 0
> >> .ShrinkToFit = False
> >> .ReadingOrder = xlContext
> >> .MergeCells = False
> >> End With
> >> ActiveWindow.ScrollColumn = 4
> >> ActiveWindow.ScrollColumn = 3
> >> ActiveWindow.ScrollColumn = 2
> >> ActiveWindow.ScrollColumn = 1
> >> Range("A2:E35").Select
> >> Selection.Copy
> >> Windows("No_Show_2008.xls").Activate
> >> ActiveWindow.WindowState = xlMaximized
> >> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
> >>
> >>
> >> ActiveSheet.Paste
> >>
> >>
> >> End With
> >>
> >> Windows("TempNoShow.xls").Close
> >>
> >> ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(1, -1).Select
> >> ActiveWorkbook.Activate
> >>
> >> Application.WindowState = xlMaximized
> >>
> >> Module3.Enable_Events
> >>
> >> End Sub
> >>
> >>
>
>
|
|
|
|
|
|
|