VB Complie error - can You Help

I have this code which copies data input from 'Adhoc' worksheets cells G9:G15
and copies them onto next available row in 'database' worksheet, column A

Sub copydata()
Application.ScreenUpdating = False
Sheets("Adhoc").Range("G9:G15").Copy
Sheets("database").Select
Range("A65536").End(xlUp).Activate
PasteSpecial = xlValues
Sheets("Adhoc").Activate
Range("G9:G15").ClearContents
Range("G9").Select
MsgBox "Copied to Database Sheet"
Application.ScreenUpdating = True
End Sub

However when I try it, the following bit is highlighted yellow

PasteSpecial = xlValues

and a 'compile error' - Expected function or variable

as I'm the novice (but getting better) anybody fix it for me ??

Thanks

If you record a macro when you do Edit|paste special|values, you'll see code
like:


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

And your line doesn't have that range in front of the pastespecial command.

But I think you may have a problem.

You have:
Range("A65536").End(xlUp).Activate
This takes you up to the last used cell in column A. That means you could be
overwriting anything that was in that cell/row.

You could do something like:

Option Explicit

Sub copydata2()
Application.ScreenUpdating = False
Sheets("Adhoc").Range("G9:G15").Copy
Sheets("database").Select
With Range("A65536").End(xlUp).Offset(1, 0)
.Activate
.PasteSpecial = xlValues
End With
Sheets("Adhoc").Activate
Range("G9:G15").ClearContents
Range("G9").Select
MsgBox "Copied to Database Sheet"
Application.ScreenUpdating = True
End Sub

But since you're just pasting values, you could avoid the .select's and
..activate's.

Option Explicit
Sub copydata3()

Dim RngToCopy As Range
Dim DestCell As Range

Application.ScreenUpdating = False

Set RngToCopy = Worksheets("adhoc").Range("G9:G15")

With Worksheets("database")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

RngToCopy.ClearContents

MsgBox "Copied to Database Sheet"

Application.ScreenUpdating = True
End Sub

Another way is to just assign the values to that destination range:

Option Explicit
Sub copydata3A()

Dim RngToCopy As Range
Dim DestCell As Range

Application.ScreenUpdating = False

Set RngToCopy = Worksheets("adhoc").Range("G9:G15")

With Worksheets("database")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

DestCell.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value _
= RngToCopy.Value

RngToCopy.ClearContents

MsgBox "Copied to Database Sheet"

Application.ScreenUpdating = True

End Sub

Anthony wrote:
>
> I have this code which copies data input from 'Adhoc' worksheets cells G9:G15
> and copies them onto next available row in 'database' worksheet, column A
>
> Sub copydata()
> Application.ScreenUpdating = False
> Sheets("Adhoc").Range("G9:G15").Copy
> Sheets("database").Select
> Range("A65536").End(xlUp).Activate
> PasteSpecial = xlValues
> Sheets("Adhoc").Activate
> Range("G9:G15").ClearContents
> Range("G9").Select
> MsgBox "Copied to Database Sheet"
> Application.ScreenUpdating = True
> End Sub
>
> However when I try it, the following bit is highlighted yellow
>
> PasteSpecial = xlValues
>
> and a 'compile error' - Expected function or variable
>
> as I'm the novice (but getting better) anybody fix it for me ??
>
> Thanks

--

Dave Peterson

Thanks Dave,
I'll give them a try

"Dave Peterson" wrote:

> If you record a macro when you do Edit|paste special|values, you'll see code
> like:
>
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> And your line doesn't have that range in front of the pastespecial command.
>
> But I think you may have a problem.
>
> You have:
> Range("A65536").End(xlUp).Activate
> This takes you up to the last used cell in column A. That means you could be
> overwriting anything that was in that cell/row.
>
> You could do something like:
>
> Option Explicit
>
> Sub copydata2()
> Application.ScreenUpdating = False
> Sheets("Adhoc").Range("G9:G15").Copy
> Sheets("database").Select
> With Range("A65536").End(xlUp).Offset(1, 0)
> .Activate
> .PasteSpecial = xlValues
> End With
> Sheets("Adhoc").Activate
> Range("G9:G15").ClearContents
> Range("G9").Select
> MsgBox "Copied to Database Sheet"
> Application.ScreenUpdating = True
> End Sub
>
> But since you're just pasting values, you could avoid the .select's and
> ..activate's.
>
> Option Explicit
> Sub copydata3()
>
> Dim RngToCopy As Range
> Dim DestCell As Range
>
> Application.ScreenUpdating = False
>
> Set RngToCopy = Worksheets("adhoc").Range("G9:G15")
>
> With Worksheets("database")
> Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
> End With
>
> RngToCopy.Copy _
> Destination:=DestCell
>
> RngToCopy.ClearContents
>
> MsgBox "Copied to Database Sheet"
>
> Application.ScreenUpdating = True
> End Sub
>
> Another way is to just assign the values to that destination range:
>
> Option Explicit
> Sub copydata3A()
>
> Dim RngToCopy As Range
> Dim DestCell As Range
>
> Application.ScreenUpdating = False
>
> Set RngToCopy = Worksheets("adhoc").Range("G9:G15")
>
> With Worksheets("database")
> Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
> End With
>
> DestCell.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value _
> = RngToCopy.Value
>
> RngToCopy.ClearContents
>
> MsgBox "Copied to Database Sheet"
>
> Application.ScreenUpdating = True
>
> End Sub
>
> Anthony wrote:
> >
> > I have this code which copies data input from 'Adhoc' worksheets cells G9:G15
> > and copies them onto next available row in 'database' worksheet, column A
> >
> > Sub copydata()
> > Application.ScreenUpdating = False
> > Sheets("Adhoc").Range("G9:G15").Copy
> > Sheets("database").Select
> > Range("A65536").End(xlUp).Activate
> > PasteSpecial = xlValues
> > Sheets("Adhoc").Activate
> > Range("G9:G15").ClearContents
> > Range("G9").Select
> > MsgBox "Copied to Database Sheet"
> > Application.ScreenUpdating = True
> > End Sub
> >
> > However when I try it, the following bit is highlighted yellow
> >
> > PasteSpecial = xlValues
> >
> > and a 'compile error' - Expected function or variable
> >
> > as I'm the novice (but getting better) anybody fix it for me ??
> >
> > Thanks
>
> --
>
> Dave Peterson
>