Hi All
I'm missing something in some code I'm writing
So the above code works almost right - upto the point were it saves.
My issues are
1 - it saves the original sheet with "FName"
What I need the save to do is to save the created sheet with the details in the mentioned cells
These cells are driven from data within the sheet and it remains once the sheet is copied out.
The copy to a new workbook works fine
The copy and paste values works
Reference's
Cells mentioned hold the file location were the master sheet is held and were the new sheet should be saved, as well as the name for the sheet
In simple terms, what I want the sheet to do is
1 - Copy a tab out into a new Workbook of its own
2 - Save the created sheet, in the location in FileRange, with the name in NameRange
I know its something simple - but I can't figure it out
Hope it is simple
Cheers
I'm missing something in some code I'm writing
Code:
Sub CopyPaste()
'
' CopyPaste Macro
'
'
Sheets("Haulage Manifest").Select 'Select the Haulage tab
Sheets("Haulage Manifest").Copy 'Copy to New Worksheet
Cells.Select 'Select all Cells
Selection.Copy 'Paste Values only
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Application.CutCopyMode = False
Dim FName As String
Dim FPath As String
Dim NameRange As Range
Dim FileRange As Range
Set NameRange = ActiveSheet.Range("C3")
Set FileRange = ActiveSheet.Range("c3")
FPath = FileRange.Text
FName = NameRange.Text
ThisWorkbook.SaveAs Filename:="FName"
End Sub
So the above code works almost right - upto the point were it saves.
My issues are
1 - it saves the original sheet with "FName"
What I need the save to do is to save the created sheet with the details in the mentioned cells
These cells are driven from data within the sheet and it remains once the sheet is copied out.
The copy to a new workbook works fine
The copy and paste values works
Reference's
Cells mentioned hold the file location were the master sheet is held and were the new sheet should be saved, as well as the name for the sheet
In simple terms, what I want the sheet to do is
1 - Copy a tab out into a new Workbook of its own
2 - Save the created sheet, in the location in FileRange, with the name in NameRange
I know its something simple - but I can't figure it out
Hope it is simple
Cheers