Thanks for that. I ran the tests and see what you mean.
I will try your first method using scope.
> Once you declare a variable inside a procedure, then that variable
supersedes
> the one you declared outside of the procedures.
> Option Explicit
> Dim s As Long
> Sub test1()
> Dim s As Long
> Dim i As Long
> For i = 1 To 10
> s = s + i
> Next i
> End Sub
> Sub test2()
> Dim i As Long
> For i = 1 To 10
> s = s + i
> Next i
> End Sub
> Sub runboth()
> Dim i As Long
> MsgBox s & " before test1"
> For i = 1 To 2
> test1
> Next i
> MsgBox s & " after test1, but before test2"
> For i = 1 To 2
> test2
> Next i
> MsgBox s & " after test2"
> End Sub
> Try running RunBoth and see what you get. test1 has no effect on the S
variable
> declared outside the procedure. But you can see that it's changing.
> But Test2 keeps making that S variable bigger and bigger.
> That's one of the advantages (or disadvantages) of using the module level
> variables. They don't get reset until you reset them yourself.
> > Hi Dave,
> > Thanks for your comments. I have had a quick read through your first
> > suggestion and will need to study it some more before I really
understand
> > it. It makes sense in that it is tying things together tightly.
> > I have dimmed s in Option Explicit general declarations but also have it
in
> > the QuerySheets sub. You say not to do this. Why is this?
> > Ta,
> > Martin
> > > I think you a problem with variable scope.
> > > Since you defined S within the QuerySheets sub, the CDiv sub doesn't
know
> > what
> > > it is.
> > > You have two choices.
> > > The first choice (maybe harder to understand, but nicer to learn):
> > > Make the called sub accept parameters that can be passed to from the
> > calling
> > > sub.
> > > Kind of:
> > > Public Sub CDiv(S As Worksheet, C As Range, D As Range, E As Range)
> > > 'your sub here
> > > end sub
> > > then instead of just calling CDiv simply like:
> > > CDiv
> > > you have to do pass it the parms.
> > > CDiv S, C, D, E
> > > I used these variable names to keep changes to your sub minimal.
> > > I would usually write
> > > Public Sub CDiv(wks As Worksheet, rng1 As Range, rng2 As Range, rng3
As
> > Range)
> > > You would still pass it just like:
> > > CDiv S, C, D, E
> > > So S would correspond with wks, C to rng1, D to rng2, and E to rng3.
> > > Inside of CDiv, you would refer to them by their local variable name.
> > > With wks
> > > and
> > > .Range("BC8", .Range("BC8").End(xlDown)).Copy rng3
> > > Take a look at Scope in the help.
> > > ============
> > > The second choice (easier, but may be a lot less useful in the long
run):
> > > Dim the variables right after your Option Explict statement at the top
of
> > the
> > > module. Instead of being local to each sub, they are now visible to
each
> > > procedure in that module.
> > > Option Explicit
> > > Dim S as worksheet
> > > Dim C as Range
> > > Dim D as range
> > > Dim E as range
> > > Public Sub QuerySheets()
> > > 'don't dim them again inside here!
> > > ...
> > > end sub
> > > public sub CDIV()
> > > 'don't dim them here either
> > > end sub
> > > > excel 2000
> > > > My code is causing data to be pasted to the wrong sheet. My app has
a
> > web
> > > > query on each sheet and I copy/paste the data into a format that I
can
> > use.
> > > > OnTime handles the refreshes. The trouble is that data on the first
> > sheet
> > > > is getting pasted to the second sheet etc. Below is a snippet of
the
> > main
> > > > procedure and a copy sub that it calls.
> > > > QuerySheets runs with "With s" but CDiv does not.
> > > > I have tried using "For Each s In ActiveWorkbook.Worksheets" in
> > QuerySheets
> > > > but that is what seems to cause the pasing in the wrong sheets.
> > > > What I want is for the app to only do one sheet at a time and then
move
> > onto
> > > > the next sheet.
> > > > Any help would be much appreciated.
> > > > Ta,
> > > > Martin
> > > > Public Sub QuerySheets()
> > > > Dim s As Worksheet
> > > > For Each s In ActiveWorkbook.Worksheets
> > > > If Left(s.Name, 1) = "R" Then
> > > > With s
> > > > Select Case ST1
> > > > Case 30 '30 minus a minute
> > > > Set C = .Range("T1")
> > > > Set D = .Range("C7")
> > > > Set E = .Range("AG7")
> > > > CDiv
> > > > Public Sub CDiv() 'Copy div into appropriate time column
> > > > Dim CopyToRan As Range
> > > > With s
> > > > If Not L Then
> > > > If s.Range("BC8") <> "" Then
> > > > If s.Range("BC9") <> "" Then
> > > > s.Range("BC8",
pasting to the wrong sheets