following is the error
Posts by xlers
-
-
i have a code to copy the active sheet multiple time(user defined) after the same active sheet but dont know why it is giving error
Sub CopysheetMultipleTimes()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim i As Variant
Dim p As Integer
Dim sh As Worksheet
Set sh = ThisWorkbook.ActiveSheet
On Error GoTo out
i = Application.InputBox("How many copies do you what?", "Rochem Inspectors")
If i = False Then GoTo out
p = 0
Do
ActiveSheet.Copy After:=ActiveWorkbook.Worksheets(sh)
p = p + 1
Loop Until p = i
Exit Sub
out:
MsgBox "Copy Was Cancelled", , "Rochem Inspectors"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
-
thanks for the help guys
-
i have a list of sheets and in front a checkbox symbol is there whoose value is R
i need a code that selects all the sheets whose checkbox is R and create a pdf for all the selected sheets combine. here is my code but it is giving error subsript out of range on sheets(sharray).select
Display MoreCode- Sub selectsheets()
- Dim x As Long, i As Long, rCount As Long, sCount As Long
- Dim mSh As Worksheet
- Dim shArr() As Variant
- Dim fSheet As String 'first shet in the range
- Dim fRun As Boolean ' check if first sheet found
- Const saveLoc As String = "C:\Users\user\Desktop\"
- Const ExportName As String = "LDG CRUDE OIL.pdf"
- Set mSh = Sheets("Cals")
- rCount = WorksheetFunction.CountIf(mSh.Range("C:C"), "R")
- fRun = False
- sCount = 1
- x = mSh.Range("A1").CurrentRegion.Rows.Count
- ReDim shArr(1 To rCount)
- For i = 1 To x
- If mSh.Range("C" & i).Value = "R" Then
- If fRun = False Then
- fSheet = Sheets(i).Name
- fRun = True
- End If
- shArr(sCount) = mSh.Range("A" & i).Value
- sCount = sCount + 1
- End If
- Next i
- Sheets(shArray).Select
- Sheets(fSheet).Activate
- ActiveSheet.ExportAsFixedFormat _
- Type:=xlTypePDF, _
- Filename:=saveLoc & ExportName
- End Sub