VBA Code To Copy Worksheet Tab To A New WorkSheet

[FLani]

Hi All,

Do you have a vba code that would copy one worksheet tab to a new worksheet and save the new worksheet to a folder? Thanks

[RHCPgergo]

Hi.

Do you mean copy to a new workbook?

[arlu1201]

Do you mean save the worksheet to a new workbook and save it to a folder?

[FLani]

Yes. Copy 1 tab of the current workbook and paste it to a new workbook. The new workbook should contain only 1 tab and the tab's cells are copy and pasted as values. Then this new workbook should be saved in a folder.

[arlu1201]

Copy this code into your current workbook -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[arlu1201]

Copy this code into your current workbook -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[FLani]

is there a way to save it looking up to an excel file in that worksheet as the folder changes according to date?

[arlu1201]

What do you mean by looking up to an excel file in that worksheet?

[FLani]

to one cell that contains formulas for the updated date for example D:\Doc\Mar\Mar03

[arlu1201]

So you will have the text "D:\Doc\Mar\Mar03" entered in one particular cell of your sheet? Which cell?

[FLani]

C96 this is what i have so far:


Sub SaveFile()

Dim OutputFP As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Calculate

OutputFP = Range("C96").Value

Sheets(Array("Deleted Adjs", "Day1")).Select
Sheets("Day1").Activate
Sheets(Array("Deleted Adjs", "Day1")).Copy
Sheets("Deleted Adjs").Select
Range("A1:BZ100").Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

Sheets("Day1").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

Sheets("Deleted Adjs").Select

'Save File
On Error GoTo ErrorRoutine
ActiveWorkbook.SaveAs Filename:= _
OutputFP _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
MsgBox "File saved."
Exit Sub

ErrorRoutine:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
MsgBox "File not saved."

End Sub

[arlu1201]

Flani,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here