Sheet Ref in VBA code reference to Cell?

[majime01]

I have a section of VBA code (thank you John Davis ) as follows:

Sub majime01()
Dim ws As Worksheet, x As String
x = Sheets("Sheet1").Range("A1")
For Each ws In Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6"))
ws.Range("B" & Rows.Count).End(3)(2) = x
Next ws
End Sub

Is it possible to have the Sheet Array reference back to another sheet's cells? For example:

For Each ws In Sheets(Array("Sheet2CellA1", "Sheet2CellA2", "Sheet2CellA3", "Sheet2CellA4", "Sheet2CellA5"))

This way the code could affect a variable of other sheets based on changing values on Sheet 2.

Thank you in advance.

[PCI]

Hi majime01,
It looks you forgot the code tags, is it?
PCI

[PCI]

Try next code
note: see the use of the code tags

Please Login or Register  to view this content.

[majime01]

Here are the two sets of code that run.

Sub AddtoAllSheets()
Dim ws As Worksheet, x As String
x = Sheets("HomePage").Range("Q12")
For Each ws In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30"))
ws.Range("B" & Rows.Count).End(3)(2) = x
Next ws
End Sub


Sub majime02()
Dim ws As Worksheet
Dim x As String
Dim WkRg
x = Sheets("HomePage").Range("Q3")
For Each WkRg In Array("Table Assignments!K17", "Table Assignments!K18", "Table Assignments!K17", "Table Assignments!K19", "Table Assignments!K20", "Table Assignments!K21")
Range(WkRg) = x
Next
End Sub



The first one works great and puts data in all 30 sheets as needed. The second one does not work. I basically need it to run just like the first one, but select workbook sheets based off of the number listed in sheet and cells"Table Assignments!K18" through K21.

Possible or ??

Thank you in advance.

[PCI]

Is it whet you want ...?
See also the file attached

Sub majime03()
Dim ws As Worksheet
Dim x As String
Dim WkRg
   x = Sheets("HomePage").Range("Q3")
   For Each WkRg In Array("'Table Assignments'!K17", "'Table Assignments'!K18", "'Table Assignments'!K17", "'Table Assignments'!K19", "'Table Assignments'!K20", "'Table Assignments'!K21")
      Sheets(CStr(Range(WkRg))).Range("B" & Rows.Count).End(3)(2) = x
   Next
End Sub 

[majime01]

Thank you PCI, I like the way the code runs. But I do have a follow-up question if I may. I noticed that if there is no data in say 'Table Assignments'!K21, (a blank cell) then it throws an error. Is there a way to adjust for that or do you always need to match the number of worksheets in the array? Thank you in advance.

[PCI]

See next code

Please Login or Register  to view this content.

[majime01]

PCI,

Amazing job. Just what I was looking for. Well done sir.

Thank you very much.

[PCI]

You are welcome
Enjoy Excel