VBA function syntax error

kottbulle86

New Member
Joined
Dec 5, 2013
Messages
2
Hello,

I'm a beginner at VBA and I'm trying to create a function that will perform a filter/copy/paste sequence that is repeated several times in my macro.

my function looks like this:

Function filtercopypaste(copysheet As String, pastesheet As String, copyrange As Range, pasterange As Range, filterrange As Range, filterinput As Variant, fieldinput As Integer)

'Filter on End_Week for Nov. and copy/paste
Sheets(copysheet).Select
ActiveSheet.Range(filterrange).AutoFilter field:=fieldinput, Criteria1:=filterinput, Operator:=xlFilterValues
Range(copyrange).Select
Selection.Copy
Sheets(pastesheet).Select
Range(pasterange).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Reset filter in sheet Servers
Sheets(copysheet).Select
Application.CutCopyMode = False
ActiveSheet.Range(filterrange).AutoFilter field:=fieldinput

End Function

My code (the relevant part) looks like this:

Sub DoStuff()

Dim csheet As String
Dim psheet As String
Dim crange As Range
Dim prange As Range
Dim frange As Range
Dim farray() As Variant
Dim finput As Integer

csheet = "Servers"
psheet = "Monthly_Report"
crange = "F4:F100"
prange = "A2"
frange = "$A$4:$AT$100"
farray = Array("2013_W.44", "2013_W.45", "2013_W.46", "2013_W.47", "2013_W.48")
finput = 15

filtercopypaste (csheet, psheet, crange, prange, frange, farray, finput)

End Sub

The "function call" turns red and the error msg "Compile error: Expected: =" pops up. Anyone knows what the problem might be?

Thank you in advance!
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

deanst

Board Regular
Joined
Nov 1, 2011
Messages
71
Hi - type Call in front of the function call.

Alternatively, remove the parentheses around now argument list

Code:
Call filtercopypaste (csheet, psheet, crange, prange, frange, farray, finput)

or

Code:
filtercopypaste csheet, psheet, crange, prange, frange, farray, finput

See reason at http://msdn.microsoft.com/en-us/library/wcx04ck5(VS.85).aspx.

Dean.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top