pasting to the wrong sheets 
Author Message
 pasting to the wrong sheets

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", s.Range("BC8").End(xlDown)).Copy D
                Else
                        s.Range("BC8").Copy D
                    End If
                End If



Thu, 08 Jul 2004 08:17:27 GMT  
 pasting to the wrong sheets
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

Quote:

> 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", s.Range("BC8").End(xlDown)).Copy D
>                 Else
>                         s.Range("BC8").Copy D
>                     End If
>                 End If

--

Dave Peterson



Thu, 08 Jul 2004 09:33:45 GMT  
 pasting to the wrong sheets
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


Quote:
> 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", s.Range("BC8").End(xlDown)).Copy
D
> >                 Else
> >                         s.Range("BC8").Copy D
> >                     End If
> >                 End If

> --

> Dave Peterson




Thu, 08 Jul 2004 09:44:29 GMT  
 pasting to the wrong sheets
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.

Quote:

> 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", s.Range("BC8").End(xlDown)).Copy
> D
> > >                 Else
> > >                         s.Range("BC8").Copy D
> > >                     End If
> > >                 End If

> > --

> > Dave Peterson


--

Dave Peterson



Thu, 08 Jul 2004 10:12:00 GMT  
 pasting to the wrong sheets
Hi Dave,
Thanks for that.  I ran the tests and see what you mean.
I will try your first method using scope.
Ta,
Martin


Quote:
> 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",

s.Range("BC8").End(xlDown)).Copy

- Show quoted text -

Quote:
> > D
> > > >                 Else
> > > >                         s.Range("BC8").Copy D
> > > >                     End If
> > > >                 End If

> > > --

> > > Dave Peterson

> --

> Dave Peterson




Thu, 08 Jul 2004 10:27:56 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Pasting data to the wrong sheet

2. Copy/paste sheet to sheet

3. copy/paste from sheet to sheet

4. Cut and paste the wrong column

5. cut/paste to wrong workbook

6. VBA Cut/Paste to wrong file

7. Excel pastes wrong data from clipboard

8. wrong date when copy and paste inter workbooks

9. Pasting wrong dates...help!

10. After pasting wrong style


 
Powered by phpBB® Forum Software © phpBB Group