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

VB Complie error - can You Help
 

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

VB Complie error - can You Help
 

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