PDA

View Full Version : Making new EXCEL file with value only by copy from other excel



tendosai
05-18-2020, 06:30 PM
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?

tendosai
05-18-2020, 06:42 PM
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.

tendosai
05-18-2020, 07:48 PM
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

paulked
05-20-2020, 04:53 PM
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