Working on the wrong sheet

[Craigm]

I am not able to manipulate data on the "Charts" worksheet.

I have three worksheets ("Charts", "Data", and "DataDown") in a single workbook.
1. The "Charts" worksheet is where the final output needs to reside.
2. The "Data" worksheet is data copied from "DataDown" and another workbook.
3. The "DownData" worksheet is a download of 49 columns.

I have entered the subroutine below on the "Data" worksheet. When the sunroutine runs, it brings the "Charts" worksheet to the front but manipulates the data on the "Data" sheet.

I need to be able to manipulate the data on the "Charts" worksheet from the "Data" worksheet. I do not even need to bring the "Charts" worksheet to the front.

I have lost my senses and cannot alter the data on "Charts".

Your thoughts are eagerly sought and appreciated!

Craigm

----------------------
Sub Acc_Types()

Dim wbBook As Workbook
Dim wsSheet As Worksheet

Dim rDescription 'Description Column

Set wbBook = Workbooks("Charts_DataDown.xls")
Set wsSheet = wbBook.Sheets("Data")

Worksheets("Charts").Activate 'Move to "Charts" worksheet

'We are manipulating the DATA Sheet NOT CHARTS sheet!
For Each rDescription In Range("A8:A10")
If rDescription.Value <> "" Then
rDescription.Value = rDescription.Value & "9999;"
End If


Next rDescription

End Sub
-----------------------------------

[bhofsetz]

Does the sub need to be in the Data worksheet.

If you put it in a standard module it should run as you want.

Alternatively if you need to keep it in the Data worksheet you could try specifying the Charts worksheet each time you want to manipulate data on it.

ie, change:

For Each rDescription In Range("A8:A10")

to:

For Each rDescription In Sheets("Charts").Range("A8:A10")

If your code is in a particular worksheet it will act on that worksheet unless explicitly to do otherwise.

HTH

[Jake Marx]

Hi Craig,

OK, a few things. First, there is no need to Activate the "Charts"
Worksheet here, as you mentioned. Second, if your code resides behind the
"Data" Worksheet and you don't full-qualify your Range references (ie,
provide the Workbook and/or Worksheet object references), the Range objects
will always refer to "Data" Worksheet.

I would recommend using a standard code module instead of putting the code
behind a Worksheet. You can do what you're looking to do with this code:

Sub Acc_Types()
Dim rDescription As Range

For Each rDescription In Worksheets("Charts" _
).Range("A8:A10")
If rDescription.Value <> "" Then
rDescription.Value = rDescription.Value & "9999;"
End If
Next rDescription
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Craigm wrote:
> I am not able to manipulate data on the "Charts" worksheet.
>
> I have three worksheets ("Charts", "Data", and "DataDown") in a single
> workbook.
> 1. The "Charts" worksheet is where the final output needs to reside.
> 2. The "Data" worksheet is data copied from "DataDown" and another
> workbook.
> 3. The "DownData" worksheet is a download of 49 columns.
>
> I have entered the subroutine below on the "Data" worksheet. When the
> sunroutine runs, it brings the "Charts" worksheet to the front but
> manipulates the data on the "Data" sheet.
>
> I need to be able to manipulate the data on the "Charts" worksheet
> from the "Data" worksheet. I do not even need to bring the "Charts"
> worksheet to the front.
>
> I have lost my senses and cannot alter the data on "Charts".
>
> Your thoughts are eagerly sought and appreciated!
>
> Craigm
>
> ----------------------
> Sub Acc_Types()
>
> Dim wbBook As Workbook
> Dim wsSheet As Worksheet
>
> Dim rDescription
> 'Description Column
>
> Set wbBook = Workbooks("Charts_DataDown.xls")
> Set wsSheet = wbBook.Sheets("Data")
>
> Worksheets("Charts").Activate 'Move to "Charts" worksheet
>
> 'We are manipulating the DATA
> Sheet NOT CHARTS sheet!
> For Each rDescription In Range("A8:A10")
> If rDescription.Value <> "" Then
> rDescription.Value = rDescription.Value & "9999;"
> End If
>
>
> Next rDescription
>
> End Sub
> -----------------------------------

[Tom Ogilvy]

Move the code to a general module, not the sheet module of the Data sheet.
--
Regards,
Tom Ogilvy

"Craigm" <[email protected]> wrote in
message news:[email protected]...
>
> I am not able to manipulate data on the "Charts" worksheet.
>
> I have three worksheets ("Charts", "Data", and "DataDown") in a single
> workbook.
> 1. The "Charts" worksheet is where the final output needs to reside.
> 2. The "Data" worksheet is data copied from "DataDown" and another
> workbook.
> 3. The "DownData" worksheet is a download of 49 columns.
>
> I have entered the subroutine below on the "Data" worksheet. When the
> sunroutine runs, it brings the "Charts" worksheet to the front but
> manipulates the data on the "Data" sheet.
>
> I need to be able to manipulate the data on the "Charts" worksheet from
> the "Data" worksheet. I do not even need to bring the "Charts"
> worksheet to the front.
>
> I have lost my senses and cannot alter the data on "Charts".
>
> Your thoughts are eagerly sought and appreciated!
>
> Craigm
>
> ----------------------
> Sub Acc_Types()
>
> Dim wbBook As Workbook
> Dim wsSheet As Worksheet
>
> Dim rDescription 'Description
> Column
>
> Set wbBook = Workbooks("Charts_DataDown.xls")
> Set wsSheet = wbBook.Sheets("Data")
>
> Worksheets("Charts").Activate 'Move to "Charts" worksheet
>
> 'We are manipulating the DATA
> Sheet NOT CHARTS sheet!
> For Each rDescription In Range("A8:A10")
> If rDescription.Value <> "" Then
> rDescription.Value = rDescription.Value & "9999;"
> End If
>
>
> Next rDescription
>
> End Sub
> -----------------------------------
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=381733
>

[Craigm]

I simply copied the code into the general module for the workbook and it runs correctly.

Outstanding, your comments were exacly on track.

Thank You!