Sheets or worksheets usage

[SmithyJD]

Hi Can someone please explain to me in simple terms the differents between using these two commands to set a cells value

Sheets("weeklyreport").Range("b" & y).Value = XXXXX
Worksheets("weeklyreport").Range("b" & zz + 7).Value = XXXXX

Also is there an equivalent that lets me update a cell in a sheet in a workbook different to the one currently active?

Thanks

JD

 

[PureSlate]

Sheets is a more all-inclusive command. For example, you need to use Sheets() instead of Worksheets() to reference a non-standard sheet, like a chart.

So far as how to update a cell in a different workbook, you should just be able to use the code you posted. For example, the following code updated cell A2 in a test book I made:

Sub Test()
Sheets("Sheet2").Range("A2").Value = "XXXXX"
End Sub

 

[SkipVought]

To reference objects in another workbook without selecting it, you must reference the workbook et al related objects to the object of interest.

When working with multiple workbooks or multiple worksheets, best to fully qualify and NOT SELECT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PureSlate]

When working with multiple workbooks or multiple worksheets, best to fully qualify and NOT SELECT.

This is an excellent point. Selecting things as you goes will slow down your code massively.

 

[SmithyJD]

OK heres the code I have

For intCount = 1 To intCountcells
CardType = Range("C" & intCount)

Select Case CardType
Case Is = " Card Creation Event"
CardId = Range("E" & intCountcells)
Sheets("Analysis").Range("A2").Value = CardId
MsgBox "Card Cteate"
End Select

Next intCount

When I run it I get a runtime Error 9 Subscript out of range and I for the live of me cant see why the work shet name (Analysis) is correct even checked for spaces or anything silly

Any ideas to prove I havent lost it greatfully received.

Jd

 

[SmithyJD]

To add a little I just discovered that

1 should be CardId=Range("E" & Intcount)
2 if In debug I make the workbook containing analysis sheet active it works but I dont want to do that.


Cheers

Jd

PS wish you could edit your own previous post if no one has replied.

 

[SkipVought]

if In debug I make the workbook containing analysis sheet active it works

To reference objects in another workbook without selecting it, you must reference the workbook et al related objects to the object of interest.

Therefore...

    For intCount = 1 To intCountcells
        CardType = [b]YourWorkbookObject.YourSheetObject.[/b]Range("C" & intCount)
        
        Select Case CardType
            Case Is = " Card Creation Event"
                CardId = [b]YourWorkbookObject.YourSheetObject.[/b]Range("E" & intCountcells)
                [b]YourWorkbookObject.[/b]Sheets("Analysis").Range("A2").Value = CardId
                MsgBox "Card Cteate"
        End Select
    
    Next intCount

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]