VBA: Copy sheet and paste as values to closed workbook

[cofracr]

Hello, I am trying to copy a sheet from one workbook to the end of another closed workbook. However, the sheet that is being copied contains formulas and I only want the values to be pasted in the closed workbook and I am not too sure how to accomplish this. Also, I am trying to rename the sheet in the closed workbook based on a cell value. My code is below:

Sub CopySheetToMaster()
Dim closedBook As Workbook
Dim currentSheet As Worksheet

Application.ScreenUpdating = False

    Set currentSheet = Application.ActiveSheet
    'select location of Master Database Workbook
    Set closedBook = Workbooks.Open("C:\Users\filepath\Master Database.xlsm")
    
    currentSheet.Copy After:=closedBook.Sheets(closedBook.Worksheets.Count) 'copy Database sheet from fieldsheet to end of sheets in Master Database Workbook
    
    'renames the new sheet in Master Database as the name in B1
    If currentSheet.Range("B1").Value <> "" Then
        On Error Resume Next
        ActiveSheet.Name = currentSheet.Range("B1").Value
    End If

    closedBook.Close SaveChanges:=True 'saves the Master Database and closes it

Application.ScreenUpdating = True

End Sub

 

[Herakles]

This is how I would do it.

You will need to change lines 5 and 6 as to fit your needs.

Test it on a copy of your workbooks.

Public Sub CopySheetToMaster()
Dim WsFrom As Worksheet
Dim WbCopyTo As Workbook

    ActiveWorkbook.Save
    
    Set WsFrom = Workbooks("CopyFrom.xlsm").Worksheets("Data")
    
    Set WbCopyTo = Workbooks.Open("C:\Dump\VBA Copy sheet and paste as values to closed workbook\CopyTo.xlsm")
    
    WsFrom.Copy after:=WbCopyTo.Sheets(Sheets.Count)
    
    With ActiveSheet
        If WsFrom.Range("B1").Value <> "" Then
            .Name = WsFrom.Range("B1").Value
        End If
        .UsedRange.Value = .UsedRange.Value
    End With
    
    ActiveWorkbook.Close SaveChanges:=True
               
    ActiveWorkbook.Save

End Sub

 

[cofracr]

This is how I would do it.

You will need to change lines 5 and 6 as to fit your needs.

Test it on a copy of your workbooks.

Public Sub CopySheetToMaster()
Dim WsFrom As Worksheet
Dim WbCopyTo As Workbook

    ActiveWorkbook.Save
   
    Set WsFrom = Workbooks("CopyFrom.xlsm").Worksheets("Data")
   
    Set WbCopyTo = Workbooks.Open("C:\Dump\VBA Copy sheet and paste as values to closed workbook\CopyTo.xlsm")
   
    WsFrom.Copy after:=WbCopyTo.Sheets(Sheets.Count)
   
    With ActiveSheet
        If WsFrom.Range("B1").Value <> "" Then
            .Name = WsFrom.Range("B1").Value
        End If
        .UsedRange.Value = .UsedRange.Value
    End With
   
    ActiveWorkbook.Close SaveChanges:=True
              
    ActiveWorkbook.Save

End Sub

This works to copy the sheet to the closed workbook but it does not paste as values in the closed workbook.

 

[Herakles]

The following line effectively removes the formulas to just leave the values.

.UsedRange.Value = .UsedRange.Value

It is not doing a paste but it copies the worksheet to the 'closed' workbook before executing the above line of code.

 

[cofracr]

The following line effectively removes the formulas to just leave the values.

.UsedRange.Value = .UsedRange.Value

It is not doing a paste but it copies the worksheet to the 'closed' workbook before executing the above line of code

Correct, however I need the worksheet (Book 1) that is going into the closed workbook (Book 2) to be values and leave the formulas in the sheet from Book 1. Right now, the code copies the sheet (Book 1) to Book 2 but leaves the formulas which causes none of the data to transfer, and then removes all the formulas in the sheet (that was copied) in Book 1.

 

[Herakles]

Let me replicate exactly what you have.

What are the two workbooks called?
The open one with the code in in and the initially closed workbook.

What is the source worksheet called in the open workbook?

What is the value in cell B1?
This is used to rename the new worksheet in the closed workbook.

 

[cofracr]

Let me replicate exactly what you have.

What are the two workbooks called?
The open one with the code in in and the initially closed workbook.

What is the source worksheet called in the open workbook?

What is the value in cell B1?
This is used to rename the new worksheet in the closed workbook.

Book with sheet being copied: "Fieldsheet Pad Totals"
Closed book: "Master Database"

Source worksheet in Fieldsheet Pad Totals: "Database"

The value in cell B1 in the Fieldsheet Pad Totals Database worksheet is a formula that pulls data from a different sheet in the same workbook.
The formula is =CONCATENATE(Job!$D$12, " Pad Database") which gives the name "Abney-T.P. Smith 1H-3H Pad Database"

 

[Herakles]

The text that you want to use as the worksheet name is 35 characters in length.
Abney-T.P. Smith 1H-3H Pad Database

When one manually creates a worksheet and paste a name of more than 31 characters, all characters after and including character 32 are omitted.

When one types the name in only 31 characters can be entered.

When one tries to do it using VBA that does not happen. An error produced.
See attached image.

Is the code that you are running stopping at this point and not running the following line:
.UsedRange.Value = .UsedRange.Value

Is an error generated as it is for me.

 

[cofracr]

The text that you want to use as the worksheet name is 35 characters in length.
Abney-T.P. Smith 1H-3H Pad Database

When one manually creates a worksheet and paste a name of more than 31 characters, all characters after and including character 32 are omitted.

When one types the name in only 31 characters can be entered.

When one tries to do it using VBA that does not happen. An error produced.
See attached image.

Is the code that you are running stopping at this point and not running the following line:
.UsedRange.Value = .UsedRange.Value

Is an error generated as it is for me.

The code was originally not stopping at the line you described which is due to the fact the formulas were copying over to the closed workbook and so the value returned by the formula in B1 was nothing which allowed the code to keep running. I actually rewrote the code to hopefully be a little more straight forward and its seems to be working.

Sub SendToMaster()

Dim wsCopy As Worksheet
Dim wbDest As Workbook

Application.ScreenUpdating = False

    Set wsCopy = Application.ActiveSheet
    Set wbDest = Workbooks.Open("C:\Users\filepath\Master Database.xlsm")
    
    wbDest.Sheets.Add After:=Sheets(Sheets.Count)
    
    wsCopy.Range("B1:DL1006").Copy
    
    With wbDest.Worksheets("Sheet2").Range("B1")
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteColumnWidths
    End With
    
    If wsCopy.Range("B1").Value <> "" Then
        On Error Resume Next
        ActiveSheet.Name = wsCopy.Range("B1").Value
    End If
    
    wbDest.Close Savechanges:=True
    
Application.CutCopyMode = False
Application.ScreenUpdating = True
       

End Sub