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

tcfreer

Board Regular
Joined
Jan 24, 2017
Messages
72
Hi All

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
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Try recording a macro and reviewing your code.

Not near a PC to answer; others on the board can help too.

A search online throws up a large number of hits on saving via VBA. Try searching for the term "VBA save as"
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

tcfreer

Board Regular
Joined
Jan 24, 2017
Messages
72
No its fine

I figured it out

I had the cell details wrong the way round so it was trying to find the details out of the folder called the name
 

Watch MrExcel Video

Forum statistics

Threads
1,129,568
Messages
5,637,094
Members
416,957
Latest member
Brovashift

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