VBA copy worksheet to new file

Shadkng · Nov 30, 2018

Hi, I would like to copy one tab/sheet form my workbook and have it saved to a new file. Can the file be saved under the tab name and placed in the same directory as the original workbook? It would be OK to keep saving over the previous file. I also need the cell formulas changed to values in the process. After searching I see this is a common need but I haven't come across exactly what I need. The closest I have found is the code below which creates the workbook but doesn't save it - I don't know too much about code so I don't know if this code is efficient. It does change the formulas to values. Any help is appreciated.

Sub SampleMacro()
Dim SrcWB As Workbook, TrgtWB As Workbook
Dim Sh As Worksheet
Dim MyArray As Variant, ShName As Variant
Dim Matched As Boolean
Application.ScreenUpdating = False
MyArray = Array("LABELS") 'Change the sheet names as required
Set SrcWB = ThisWorkbook
Set TrgtWB = Workbooks.Add
Application.CopyObjectsWithCells = False
SrcWB.Worksheets(MyArray).Copy Before:=TrgtWB.Worksheets(1)
Application.CopyObjectsWithCells = True
For Each Sh In TrgtWB.Worksheets
With Cells
.Copy
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
End With
Range("A1").Select
Matched = False
For Each ShName In MyArray
If ShName = Sh.Name Then
Matched = True
Exit For
End If
Next
If Not Matched Then
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End If
Next Sh
Application.CutCopyMode = False
Application.ScreenUpdating = False
End Sub

mumps · Dec 2, 2018

Try:

Code:

Sub SaveSheet()
    Application.ScreenUpdating = False
    Dim shp As Shape
    Sheets("LABELS").Copy
    ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\Steve\Desktop\Dropbox\Quotes Steve\LABELS\" & ActiveSheet.Name & ".xlsx"
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Shadkng · Dec 4, 2018

Thanks perfect!

mumps · Dec 4, 2018

Glad it worked out.

Shadkng · Dec 14, 2018

Hi, I am having a small problem with this code. After the sheet is copied into the new file, we use a label program to import the data to print labels. For some reason the program is picking up the blank rows below the data and importing hundreds of blank entries - but the rows do not seem to have anything in them. So is there a way to avoid this problem? If I copy just the data into a new sheet it works fine. Thanks

mumps · Dec 14, 2018

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Shadkng · Dec 14, 2018

I don't think I can do that. It's a very large workbook with a lot of information and the sheet I am talking about has formulas referring to many other sheets. Is there a way to first create the new file and then copy and paste as values into the newly created sheet? Or maybe after the new file is created it copies the range into a new sheet as values. That does work because I tried it manually. The problem is not what you wrote but with the label program picking up blank cells. I van mess with more and maybe can give you more info to help solve. Thanks

mumps · Dec 15, 2018

This line of code:

Code:

ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value

already copies/pastes the values thus eliminating the formulas. Try this macro to see if it works for you. Change the "A" (in red) in the code to a any column that contains a formula.

Code:

Sub SaveSheet()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim shp As Shape, LastRow As Long, x As Long
    Sheets("LABELS").Copy
    LastRow = Range("[COLOR="#FF0000"]A[/COLOR]" & Rows.Count).End(xlUp).Row
    ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
    For x = LastRow To 1 Step -1
        If Range("[COLOR="#FF0000"]A[/COLOR]" & i).HasFormula() = True Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\Steve\Desktop\Dropbox\Quotes Steve\LABELS\" & ActiveSheet.Name & ".xlsx"
    ActiveWorkbook.Close
    With Application
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Shadkng · Dec 15, 2018

It's getting stuck at this line.

If Range("D" & i).HasFormula() = True Then

I've been testing different things and found that when I delete all the rows below the last line of values my label program only imports the lines with data. If I copy just the range of data into a new sheet it works. So it seems then the entire sheet exported into a new workbook that the blank cells are marked somehow and the label program is picking them up. I think I kind off understand what your doing in the new code but not totally - what does the "i" mean in the above line? I know you said change the "A" to any column with a formula so I changed it to D which always has a formula.

Shadkng · Dec 15, 2018

Doing the following manually works. Once the rows in the original file are populated, I hi-light all the rows below the last line using shift/ctrl and down arrow, then I hit delete. As you know the range changes each time. Then I run the macro to save the sheet as a file and it works. I don't understand code that well but if that's what you are doing than it should work.

TotallyConfused · Dec 15, 2018

[FONT=&quot]I’ve been following this with great interest as I’m fairly new to Excel and VBA, so I’ve been reading the answers in the hope of learning a few new tricks. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Shadkng: In your posting #18 where you asked what the ‘i’ meant in Mumps’ formula of ‘If Range(“D” & i).HasFormula() = True Then’, I have a feeling that may have been a typing error. I suspect that ‘i’ should have been an ‘X’, since that line is part of a ‘For X =… Next’ loop. I would think that could also explain why the code stops at that point, since in effect, there is no ‘X’.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]The part I haven’t figured out yet is from posting #17 [/FONT]
[FONT=&quot] [/FONT]

Code:

[FONT=&quot]ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value[/FONT]

  [FONT=&quot]    For x = LastRow To 1 Step -1[/FONT]
  [FONT=&quot]        If Range("[COLOR=red]A[/COLOR]" & i).HasFormula() = True Then[/FONT]
  [FONT=&quot]            Rows(x).EntireRow.Delete[/FONT]
  [FONT=&quot]        End If[/FONT]
  [FONT=&quot]    Next x[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]If that first line copies the value over the formula’s, in effect destroying the formula as has been stated in posting #17 , that should mean that the ‘If Range(“A” &... ‘ line would never be true, as there would be nothing to delete. If that is true, what is the purpose of the ‘For loop’? However, as I’ve stated, I’m a beginner, so I’m hoping by the time this thread is completed, I’ll have a better understanding.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]TotallyConfused[/FONT]

VBA copy worksheet to new file

Shadkng

Active Member

mumps

Well-known Member

Excel Facts

Shadkng

Active Member

mumps

Well-known Member

Shadkng

Active Member

mumps

Well-known Member

Shadkng

Active Member

mumps

Well-known Member

Shadkng

Active Member

Shadkng

Active Member

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

VBA copy worksheet to new file

Active Member

Well-known Member

Excel Facts

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Active Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock