CODEWITHSUNDEEP
vbaexcelscripting
pjj
pjj

Reputation: 499

excel vba loop through worksheets and set values according to input

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

Upvotes: 0

Views: 4246

Answers (2)

Reafidy
Reafidy

Reputation: 8431

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

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

Which will be much faster

Upvotes: 2

paxdiablo
paxdiablo

Reputation: 881293

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.

Upvotes: 2

Related Questions