If worksheet name contains text then copy paste value columns

[jaime1182]

Hi everyone.

I am having trouble trying to get a line of code to run.

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*INV*" Or ws.Name Like "*STM*" Then
        ws.Columns("A:D").Copy
        ws.Columns("A:D").PasteSpecial Paste:=xlPasteValues
        ws.Range("A1:P1").Copy
        ws.Range("A1:P1").PasteSpecial Paste:=xlPasteValues
    Next ws
 End If

What I want to do is if the tab name contains "INV" or "STM", I need to copy paste the values of columns A:D as well as A1:P1.

I am getting a "Compile error. Next without For" prompt at debug.

I don't know if my logic is wrong because I tried to troubleshoot by changing the command to hiding the columns and the debug did not turn up.

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*INV*" Or ws.Name Like "*STM*" Then ws.Columns("A:D").Hidden = True
    Next ws
 End If

Where am I going wrong?

Would appreciate any help. Thanks in advance.

 

[jolivanes]

Try this. (Not Tested)

Sub Maybe()
Dim ws As Worksheet, i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        If InStr(ws(i).Name, "INV") <> 0 Or InStr(ws(i).Name, "STM") <> 0 Then
            With ws
                .Range("A1:D1").Resize(.UsedRange.Rows.Count).Value = .Range("A1:D1").Resize(.UsedRange.Rows.Count).Value
                .Range("E1:P1").Value = .Range("E1:P1").Value
            End With
        End If
    Next i
End Sub

 

[Peter_SSs]

Where am I going wrong?

These two lines are in the wrong order. Just swap them.

 

[jaime1182]

Apologies for the late response (had a medical emergency). Thanks guys! I'll give this a go!

 

[Peter_SSs]

No problem re timing. Glad to see you back.