Fault in code

[femma]

hi!

Can't seem to find the fault in my code. Returns an Object required -error.
I think the problem lies where it finds the sheet with the *support*-part and the sets it as the active worksheet...

Sub help()

Dim wscount As Integer
Dim ws As Worksheet

wscount = ThisWorkbook.Worksheets.Count
For i = 2 To wscount
    If Worksheet.Name Like "*Support*" Then
            Set ws = ActiveWorksheet
            Set ws1 = Sheets("Data")
            ws.[B15].Value = ws1.[E5].Value
            ws.[B21].Value = ws1.[E6].Value
            ws.[B43].Value = ws1.[E10].Value
            ws.[B44].Value = ws1.[E11].Value
            ws.[B45].Value = ws1.[E12].Value
            ws.[B47].Value = ws1.[E13].Value
            ws.[B15].Value = ws1.[E5].Value
        End If
        
Next i
            

End Sub

 

[FormR]

Hi, try changing that line to:

If Worksheets(i).Name Like "*Support*" Then

 

[Joe4]

You are never moving off the original sheet.

Try this:

Sub help()

Dim wscount As Integer
[COLOR=#ff0000]Dim i As Integer[/COLOR]
Dim ws As Worksheet
[COLOR=#ff0000]Dim ws1 As Worksheet[/COLOR]

wscount = ThisWorkbook.Worksheets.Count
For i = 2 To wscount
[COLOR=#ff0000]    If Sheets(i).Name Like "*Support*" Then
            Set ws = Sheets(i)[/COLOR]
            Set ws1 = Sheets("Data")
            ws.[B15].Value = ws1.[E5].Value
            ws.[B21].Value = ws1.[E6].Value
            ws.[B43].Value = ws1.[E10].Value
            ws.[B44].Value = ws1.[E11].Value
            ws.[B45].Value = ws1.[E12].Value
            ws.[B47].Value = ws1.[E13].Value
            ws.[B15].Value = ws1.[E5].Value
        End If
        
Next i
            
End Sub

 

[steve the fish]

You need to use

Sheets(i).Name

not Worksheet.Name. Then use ActiveSheet for your ws variable.

 

[Joe4]

Hi, try changing that line to:

Code:
If Worksheets(i).Name Like "*Support*" Then

You would also need to change the next line setting the ws variable, as looping through the sheet numbers does NOT select each sheet. So the ActiveWorksheet would still be the initial sheet you were on when you started the macro. See how I did it in my code above.

 

[Joe4]

Then use ActiveSheet for your ws variable.

Nope. Won't work for the reasons I stated above.
Looping through the sheet names does NOT actually loop through the sheets!
So the ActiveSheet never changes, unless you explicitly select each sheet as you loop through them.
Just use:

Set ws = Sheets(i)

 

[steve the fish]

Yeah i wasnt reading. I just saw ActiveWorksheet and that doesnt exist. Well it doesnt in version 7 anyway.

 

[femma]

You guys are THE BEST!! Thank you so much!

 

[FormR]

You would also need to change ...

Yes, I didn't delve too deeply past the reported problem. I'd still recommend using Worksheets(i) versus Sheets(i) though. There might not be any sheets that aren't worksheets but I don't think that's a given.