0

I'm trying to loop thru each worksheet and set 100 rows of column A to the value of an input parameter. I want each sheet to call an input box and have the column's values equal to that input but what is happening is that the 1st worksheet is getting the last input and all the sheets values for columnA are blank except for sheet 1.

Dim wkbkorigin As Workbook
Set wkbkorigin = Workbooks.Open("C:\bookB.xls")

For Each ThisWorkSheet In wkbkorigin.Worksheets  
    Subject = InputBox("Enter the 'Subject' field for " & ThisWorkSheet.Name & ":")
    For i = 1 To 100  
        Range("A2").Cells(i, 1) = Subject
    Next
Next
pnuts
  • 58,317
  • 11
  • 87
  • 139
pjj
  • 499
  • 1
  • 5
  • 4

2 Answers2

2

You may want to change:

Range("A2").Cells(i, 1) = Subject

to:

ThisWorkSheet.Range("A2").Cells(i, 1) = Subject

Range on its own will apply to the current worksheet (which, from your symptoms, is the first worksheet) so what you're doing is simply overwriting the cells in that worksheet each time.

That's why they end up with the last value entered, while the other sheets remain blank.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
2

Also, there is no need to loop - simply use:

ThisWorkSheet.Range("A2:A100").Value = Subject

Which will be much faster

Reafidy
  • 8,240
  • 5
  • 51
  • 83