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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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

Board Regular
Joined
Jan 24, 2017
Messages
72
Yes apologies - It should be C2 and C3

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

Code:
.Cells.Value = .Cells.Value
 

tcfreer

Board Regular
Joined
Jan 24, 2017
Messages
72
A bugger just realised I need to put C2 and C3 don't I

Sorry muppet mistake
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
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

Board Regular
Joined
Jan 24, 2017
Messages
72

ADVERTISEMENT

Cheers Jack

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

Everything else defo works
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 24, 2017
Messages
72
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

Board Regular
Joined
Jan 24, 2017
Messages
72
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,869
Members
417,174
Latest member
diegomuser

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