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

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

"Patrick C. Simonds" wrote:

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

Thank you, that did the trick.

"FSt1" wrote in message
...
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

"Patrick C. Simonds" wrote:

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

you're welcome
Regards
FSt1

"Patrick C. Simonds" wrote:

Thank you, that did the trick.

"FSt1" wrote in message
...
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

"Patrick C. Simonds" wrote:

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