Consulting

Results 1 to 4 of 4

Thread: Making new EXCEL file with value only by copy from other excel

  1. #1

    Making new EXCEL file with value only by copy from other excel

    Greeting everyone,
    I have a bit of complicated manual work thus i wanna make a VBA to handle the task with a click.
    I have one master EXCEL file namely "MASTER.xlsm" it contains many sheet with both value and formula to calculate another table for different sheet. Manually, I would have to create a copy to new workbook then copy/paste value only to remove the formula. This took me a while because i have to make 3 to 4 new workbook with no formula.

    The macro i try to config should be able to make new 3 new workbooks (different name / different sheet) base on "MASTER.xlsm" with all format and value intact but no formula in there.
    I found out about this
    worksheet.UsedRange.Value = worksheet.UsedRange.Value

    but does this also copy the format as well?

    shall i first make sheet copy to new workbook then use above code or maybe there is a way that both copy sheet to new workbook with all format intact but no formular?
    Last edited by tendosai; 05-18-2020 at 06:33 PM. Reason: error format

  2. #2
    Sub CopyWorksheetValues()    Dim ws As Worksheet
        Dim folderPath As String
        folderPath = Application.ThisWorkbook.Path
        Worksheets(Array("Keep1", "Keep2")).Copy
        ws.Cells.Copy
        ws.Range("A1").PasteSpecial Paste:=xlPasteValues
        ws.Application.CutCopyMode = False
        With ActiveWorkbook
         .SaveAs Filename:=folderPath & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook
         .Close SaveChanges:=False
        End With
    End Sub
    I try to config this tho it got error of variable. This will make new excel file namely New3.xlsx but the it also copy all the formula too.
    Last edited by tendosai; 05-18-2020 at 07:05 PM.

  3. #3
    Option Explicit
    Sub CopyWorksheetValues()
        Dim ws As Worksheet
        Dim WS_Count As Integer
        Dim I As Integer
        Dim folderPath As String
        folderPath = Application.ThisWorkbook.Path
        Worksheets(Array("Keep1", "Keep2")).Copy
        With ActiveWorkbook
        WS_Count = .Worksheets.Count
            For I = 1 To WS_Count
            .Sheets(I).UsedRange.Value = .Sheets(I).UsedRange.Value
            Next
         .SaveAs Filename:=folderPath & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook
         .Close SaveChanges:=False
        End With
    End Sub
    Finally i got it works but how can i make it generate 3 excel instead of one
    Last edited by tendosai; 05-18-2020 at 08:22 PM.

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    Option Explicit
    Sub CopyWorksheetValues()
        Dim ws As Worksheet
        Dim WS_Count As Integer
        Dim I As Integer
        Dim folderPath As String
        folderPath = Application.ThisWorkbook.Path
        Worksheets(Array("Keep1", "Keep2")).Copy
        With ActiveWorkbook
        WS_Count = .Worksheets.Count
            For I = 1 To WS_Count
            .Sheets(I).UsedRange.Value = .Sheets(I).UsedRange.Value
            Next
         .SaveAs Filename:=folderPath & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook
    'Make whatever changes here
         .SaveAs Filename:=folderPath & "\New4.xlsx", FileFormat:=xlOpenXMLWorkbook
    'Make even more changes here
         .SaveAs Filename:=folderPath & "\New5.xlsx", FileFormat:=xlOpenXMLWorkbook
    
         .Close SaveChanges:=False
        End With
    End Sub
    Semper in excretia sumus; solum profundum variat.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •