atinderjaggi
New Member
- Joined
- Aug 25, 2021
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
hi there, i have a code which was given to me by a member here itself. This code goes through a loop and saves the file as a pdf. instead of saving as a pdf i would like to copy that sheet (tab) and save it as a new excel file. any help is appreciated
Option Explicit
Sub createpdf3()
Dim rng As Range
Dim dataValidationArray As Variant
Dim i As Integer
Dim rows As Integer
Set rng = Sheets("Sheet3").Range("C7")
'If Data Validation list is not a range, ignore errors
On Error Resume Next
rows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.Count
ReDim dataValidationArray(1 To rows)
For i = 1 To rows
dataValidationArray(i) = _
Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)
Next i
'If not a range, then try splitting a string
If Err.Number <> 0 Then
Err.Clear
dataValidationArray = Split(rng.Validation.Formula1, ",")
End If
'Some other error has occured so exit sub
If Err.Number <> 0 Then Exit Sub
'Reinstate error checking
On Error GoTo 0
'Loop through all the values in the Data Validation Array
For i = LBound(dataValidationArray) To UBound(dataValidationArray)
'Change the value in the data validation cell
rng.Value = dataValidationArray(i)
'Force the sheet to recalculate
Application.Calculate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:="C:\Users\sobyvinnie\OneDrive\Desktop\Macro Test " & rng.Value & ".pdf", IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i
End Sub
Option Explicit
Sub createpdf3()
Dim rng As Range
Dim dataValidationArray As Variant
Dim i As Integer
Dim rows As Integer
Set rng = Sheets("Sheet3").Range("C7")
'If Data Validation list is not a range, ignore errors
On Error Resume Next
rows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.Count
ReDim dataValidationArray(1 To rows)
For i = 1 To rows
dataValidationArray(i) = _
Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)
Next i
'If not a range, then try splitting a string
If Err.Number <> 0 Then
Err.Clear
dataValidationArray = Split(rng.Validation.Formula1, ",")
End If
'Some other error has occured so exit sub
If Err.Number <> 0 Then Exit Sub
'Reinstate error checking
On Error GoTo 0
'Loop through all the values in the Data Validation Array
For i = LBound(dataValidationArray) To UBound(dataValidationArray)
'Change the value in the data validation cell
rng.Value = dataValidationArray(i)
'Force the sheet to recalculate
Application.Calculate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:="C:\Users\sobyvinnie\OneDrive\Desktop\Macro Test " & rng.Value & ".pdf", IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i
End Sub