I'm trying to create multiple sheets from a master spreadsheet and am up to the last few steps but unfortunately, I keep getting the above error? Can someone pleasee help me? Thank you
- The error line is showing the below as the problem: Sheet1.Range(Cells(10, 1), Cells(count_row, count_col)). _ SpecialCells(xlCellTypeVisible).Copy
Please see full code below:
Sub copy_data_2_new_sheets()
Dim count_col As Integer
Dim count_row As Integer
Dim dep As String
Dim check As Integer
check = 0
dep = Sheet1.Cells(2, 6).Text
On Eroor GoTo oops
Sheets.Add(AFTER:=Sheets(Sheets.Count)).Name = dep
check = 1
oops:
If check = 0 Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = False
End If
Sheets(dep).Cells.ClearContents
Sheet1.Activate
count_col = WorksheetFunction.CountA(Range("A1", Range("A10").End(xlToRight)))
count_row_ = WorksheetFunction.CountA(Range("A1", Range("A10").End(xlDown)))
ActiveSheet.Range("A10").AutoFilter Field:=2, Criteria1:=dep
Sheet1.Range(Cells(10, 1), Cells(count_row, count_col)). _
SpecialCells(xlCellTypeVisible).Copy
Sheets(reg).Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheet1.ShowAllData
Sheet1.AutoFilterMode = False
End Sub
