question about worksheet & worksheets when declare variable

[abdelfattah]

hi, experts

I have dilemma I design code to copy data from userform to sheet based on sheet name first when I declare the variables wk1,wk2 as worksheets it gives me error mismatch in this line

Set wk2 = Sheets("NEW"

this is a part of code

Dim lr1, lr2 As Integer

Dim wk1, wk2 As Worksheets

Set wk1 = Sheets("expirity")

Set wk2 = Sheets("NEW")

and when I change declaring worksheets to worksheet the code works , why?
I appreciate if anybody help me to understand what's the problem

 

[MARK858]

WorkSheets is the WorkSheet collection, it is not a data type.

Btw

Dim wk1, wk2 As Worksheet

is declaring wk1 as Variant. It should be

Dim wk1 As Worksheet, wk2 As Worksheet

 

[abdelfattah]

so I'm wrong as far I understand when use many sheets 1,2,3 ...etc it should use worksheets when declarer variables if I'm wrong , when used it so?

 

[MARK858]

When you declare a variable you are declaring it's data type not how many objects (in this case Worksheets) there are.

Each worksheet is an object, if there are are more than one then together they are a collection of worksheets.
You would use WorkSheets for example when you want to loop through the collection i.e. something like...

Dim ws as WorkSheet
For each ws in WorkSheets
  MsgBox ws.name
Next

 

[abdelfattah]

now I got it thanks very much

 

[MARK858]

You're welcome