I have a subtotal for range of data (sheet name workings) , however need to copy specific columns with visible rows to another sheet called (summary ).
See attached expected results of summary sheet in same workbook
Looking for a vba code to copy to last data range to a new sheet in same workbook.
As below code used but getting runtime error 9 script out of range![Confused](https://www.excelforum.com/images/smilies/confused.gif)
Can someone assist what going wrong
Sub visibletotals()
Dim x As Range, i As Long, y As Long, ws As Worksheet
Set ws = ActiveSheet
With ws
For i = 1 To .UsedRange.Columns.Count + 1
Select Case .Cells(1, i)
Case Is = "Tariff No Description Origin", "Qty_1", "Litres", "Sugar", "% alcohol", "Amount", "Itemised"
y = .Cells(Rows.Count, i).End(3).Row
MsgBox Sheets("Summary").Name
Set x = Sheets("Summary").Rows(1).Find(.Cells(1, i), lookat:=xlValues, LookIn:=xlWhole)
If Not x Is Nothing Then
.Range(.Cells(2, i), .Cells(y, i)).SpecialCells(12).Copy Sheets("Summary").Cells(2, x.Column)
End If
Set x = Nothing
End Select
Next i
End With
End Sub
Bookmarks