0

I have an excel file with .xls format and I want to export some sheets from that workbook to create a new workbook at specific location in .xlsx or .xlsm format.

Currently I have a macro which creates a new workbook but in the same format .xls and every time I change format in the macro and then run it. It generates a excel but when I try to open It gives me error that file format is not compatible. ( Also, I can't change format of the .xls file because it is linked to other tools)

Sub Macro20()
'

'check voor deleten links/indien ok verwijder dan onderstaande tekst :
'      "Windows("dpm.xls").Activate" en start opnieuw

        
    On Error Resume Next

    b = Str(Month(Date))
    If Len(b) > 2 Then m = Right(b, 2) Else m = "0" + Right(b, 1)
    a = Str(Day(Date))
    If Len(a) > 2 Then d = Right(a, 2) Else d = "0" + Right(a, 1)
    y = Right(Str(Year(Date)), 2)
    If m > "05" Then fy = y + 1 Else fy = y
    fy = Str(fy)
    If Len(fy) > 2 Then fy = Right(fy, 2) Else fy = "0" + Right(fy, 1)
    
'    fname = "\\Laakdal-nss-02\shareddata\Shared.All\CSC\Distribution planning\DPM\FISC" + fy + "\" + m + y + "\" + d + m + ".xls"
    
'    ActiveWorkbook.SaveAs Filename:=fname
'    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    
    fname1 = "T:\Apparel\20 Planning\20 Gegevens\60 Rapporten\DPM\FISC" + fy + "\" + m + y + "\" + d + m + ".xls"
    
    ActiveWorkbook.SaveAs Filename:=fname1
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    
    Sheets("Overview").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Invul").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Outbound").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Inbound").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Pickpool").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Inventory").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Samples").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("data").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("ART").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
       
    Sheets("copy").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Safety").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("check").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("info").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("VAS").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Checklist WE").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("insert workday").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("info1").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("makecopy").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   
    Sheets("data").Select
    Cells.Select
    Range("G108").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("ART").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("copy").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("Safety").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("check").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("info").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("VAS").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("Checklist WE").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("insert workday").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("info1").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("makecopy").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("makecopy2").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Sheets("dayswork").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Safety").Select
    ActiveWindow.SelectedSheets.Visible = False
    
    Sheets("Inventory").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll ToRight:=-8
    Sheets("launch").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll ToRight:=-8
    Sheets("Safety").Select
    ActiveWindow.SmallScroll Down:=-23
    ActiveWindow.SmallScroll ToRight:=-6
    Sheets("Meas").Select
    ActiveWindow.SmallScroll Down:=-23
    ActiveWindow.SmallScroll ToRight:=-11
    Sheets("AST").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll ToRight:=-6
    Sheets("PickPool").Select
    ActiveWindow.SmallScroll Down:=-23
    ActiveWindow.SmallScroll ToRight:=-9
    Sheets("Outbound").Select
    ActiveWindow.SmallScroll Down:=-32
    ActiveWindow.SmallScroll ToRight:=-6
    Sheets("Inbound").Select
    ActiveWindow.SmallScroll Down:=-23
    ActiveWindow.SmallScroll ToRight:=-7
    Sheets("Overview").Select
    ActiveWindow.SmallScroll Down:=-75
    ActiveWindow.SmallScroll ToRight:=-1
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Menu").Select
    Sheets("Menu").Unprotect
    ActiveWindow.TabRatio = 0.133
    Range("J30:J32").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveSheet.Shapes("AutoShape 32").Select
    Selection.Cut
    Range("D14").Select
    Sheets("Menu").Protect
    ActiveWorkbook.Save
    
    fname = "\\Laakdal-nss-02\Shareddata\Shared.All\CSC\Distribution planning\Integrated Distribution Report\Input data\DPM Apparel.xlsx"
    
    ActiveWorkbook.SaveAs Filename:=fname
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast

        
    ActiveWorkbook.SaveAs Filename:=fname1
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast

End Sub
Geert Bellekens
  • 12,788
  • 2
  • 23
  • 50
Priyanka Kochar
  • 1
  • 3
  • [Without code, it is very hard to help you](http://idownvotedbecau.se/nocode/) – Pᴇʜ Nov 17 '20 at 15:47
  • I have added the code as well. – Priyanka Kochar Nov 17 '20 at 15:57
  • Use the `FileFormat` parameter of `Workbook.SaveAs`. – BigBen Nov 17 '20 at 15:59
  • Reading the documentation will clarify: [Workbook.SaveAs method](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas). And you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Nov 17 '20 at 16:00

0 Answers0