VBA Copy a set Worksheet, Paste Values of that sheet and save with details from cells

[tcfreer]

Hi All

I'm missing something in some code I'm writing

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

 

[JackDanIce]

You're aware FileRange and NameRange in your posted code, both point to cell C3 so FileRange and NameRange will have the same values.

Anyway, untested, but try:

Sub CopyPaste2()
    
    Application.ScreenUpdating = False
    
    Sheets("Haulage Manifest").Copy
    
    With ActiveSheet
        .Cells.Value = .Cells.Value
        ActiveWorkbook.SaveAs .Cells(3, 3).Text
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    
End Sub

 

[tcfreer]

Yes apologies - It should be C2 and C3

I get a out of memory debug error on the following row

.Cells.Value = .Cells.Value

 

[tcfreer]

A bugger just realised I need to put C2 and C3 don't I

Sorry muppet mistake

 

[Norie]

There shouldn't be quotes around 'FName' when trying to save.

 

[JackDanIce]

Try:

Sub CopyPaste2()
    
    Dim x       As Long
    Dim y       As Long
    
    Dim strFile As String
    Dim strName As String
    
    Application.ScreenUpdating = False
    
    Sheets("Haulage Manifest").Copy
    
    With ActiveSheet
        x = .Cells.find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).row
        y = .Cells.find(what:="*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
        
        .Cells(1, 1).Resize(x, y).Value = .Cells(1, 1).Resize(x, y).Value
        
        '.cells(x, y) = row x column y, e.g. A1 is cells(1,1)
        strFile = .Cells(2, 3).Value
        strName = .Cells(3, 3).Value
        
        ActiveWorkbook.SaveAs strFile & strName
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    
End Sub

 

[tcfreer]

Cheers Jack

I think it works - but I'm not sure were it saves?

Everything else defo works

 

[JackDanIce]

It saves to whatever values are in C2 and C3. Press F12 after the macro finishes running; it should bring up the SaveAs dialog box with the newly created workbook name ready to write over with the folder location the new one is currently saved in.

 

[tcfreer]

Right found were it it saves but its not in the right location - its saving to my documents?

The folder it should be in is in cell C3 on the sheet - This is the current contents H:\Business Intelligence\Scott\Haulage Template

 

[tcfreer]

So Cell 2 has "\Filename"

Cell 3 has "H:\Business Intelligence\Scott\Haulage Template

It gets Error 1004 something about not finding the folder or file name?