VBA to copy sheet as a new workbook

atinderjaggi

New Member
Joined
Aug 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,073
Office Version
  1. 365
Platform
  1. Windows
This is the way

VBA Code:
Sub jec()
  With ThisWorkbook
    .Sheets(1).Copy
    ActiveWorkbook.SaveAs .Path & "\" & "blablabla", 51
  End With
End Sub
 

atinderjaggi

New Member
Joined
Aug 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thank you for your response but the formula is not working perfectly.
a) I need it to be saved to a specific path
b) I would like to save Sheet3 which is defined in my formula above but your vba is saving the first tab of my sheet.
c) I would like the range ID to be the file name

Thanks
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,972
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
See if this code works for you:

VBA Code:
    Dim FName   As String
    Dim FPath   As String
    Dim NewBook As Workbook
 '
    FName = rng.Value & ".xlsx"                                     ' Set New workbook name
    FPath = "C:\Users\sobyvinnie\OneDrive\Desktop\Macro Test\"      ' Set path to save New workbook into
 '
    Set NewBook = Workbooks.Add                                     ' Open a new workbook
 '
    ThisWorkbook.Sheets(rng.Value).Copy Before:=NewBook.Sheets(1)   ' Copy sheet to New workbook
 
    If Dir(FPath & FName) <> "" Then                                ' Check to see if new workbook name already exists
        MsgBox "File " & FPath & FName & " already exists"          '   If it does, display message
    Else                                                            '   Else
        NewBook.SaveAs Filename:=FPath & FName                      '   Save the New workbook
    End If
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,073
Office Version
  1. 365
Platform
  1. Windows
Yes, it was an example. You have to adapt the code
 

Forum statistics

Threads
1,181,673
Messages
5,931,352
Members
436,787
Latest member
ogharipour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top