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

 

[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]

 

[Tom Ogilvy]

Move the code to a general module, not the sheet module of the Data sheet.

 

[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

 

[Craigm]

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

Outstanding, your comments were exacly on track.

Thank You