Thread: Copying data as values from one Sheet to another? using a Command Button VBA

Sorry for the later response, I tried the File but it did not open and got an error END DEBUG, I could not really understand it completely, though I must say you have put a lot of efforts but it is still not meeting the requirements..

Do you have both files present in the same folder?

I feel there is not a need of Macros in both the Files, I will explain again as I feel that you have been putting a lot of efforts but unfortunately its getting complicated.

No, there is no need for both files to have macros. I just put the macros into both so that you could see your options. Both macros work independantly of each other. It's up to you to choose which will work for your needs.

Please if you can explain the VBA code it will be convenient incase I need to modify as per the requirements I can do it then...

Code:

Sub CopySheet()

' Identify variables for use
    Dim SheetName As String

' define Sheetname as the value in cell A4.  A4 is a dropdown list of customer names.
    SheetName = Range("A4").Value
        

' Open the customers workbook.  The customers workbook must be in the same folder as the Trial workbook.  
' Otherwise, "ActiveWorkbook.path needs to be changed.
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\Customers.xls"

' Copy the desired customer sheet (sheetname taken from cell A4 of the Trial workbook) and place copy 
' in the Trial workbook after the "Control" sheet.
    Sheets(SheetName).Copy After:=Workbooks("Trial.xls").Sheets("Control")

' Rename the copied sheet to reflect the customer's name, followed by " Trial" to show that this sheet is a
' trial balance sheet.
    ActiveSheet.Name = SheetName & " Trial"

' The active worksheet at the moment is the copied and renamed worksheet.  The below code replaces the 
' formulas in the range with the values.  The range is currently A:S, but can be changed to suit your needs.  
' You could use "Cells.formula" and "Cells.value", but this can take a few minutes to process.  I narrowed the 
' range to A:S to cut out some processing time.
    ActiveSheet.Range("A:S").Formula = ActiveSheet.Range("A:S").Value

' Close the customers workbook since we no longer need it.        
    Workbooks("Customers.xls").Close
    
' Make the Control worksheet active since that is where we started.
    Sheets("Control").Activate
    
' Display a message that lets the user know that the copying is complete.
    MsgBox ("Copy complete")
    
End Sub