Me gustaria que me ayudaran. Tengo esta macro de Ron de Bruin, que coge cada hoja de un libro y crea un libro nuevo con cada una de ellas sin formulas, me gustaria que hiciera lo mismo pero en un solo libro.
Sub Copy_Every_Sheet_To_New_Workbook()[color=#000000]'Working in 97-2010[/color] Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim sh As Worksheet Dim DateString As String Dim FolderName As String With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With [color=#000000]'Copy every sheet from the workbook with this macro[/color] Set Sourcewb = ThisWorkbook [color=#000000]'Create new folder to save the new files in[/color] DateString = Format(Now, "yyyy-mm-dd hh-mm-ss") FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString MkDir FolderName [color=#000000]'Copy every visible sheet to a new workbook[/color] For Each sh In Sourcewb.Worksheets [color=#000000]'If the sheet is visible then copy it to a new workbook[/color] If sh.Visible = -1 Then sh.Copy [color=#000000]'Set Destwb to the new workbook[/color] Set Destwb = ActiveWorkbook [color=#000000]'Determine the Excel version and file extension/format[/color] With Destwb If Val(Application.Version) < 12 Then [color=#000000]'You use Excel 97-2003[/color] FileExtStr = ".xls": FileFormatNum = -4143 Else [color=#000000]'You use Excel 2007-2010[/color] If Sourcewb.Name = .Name Then MsgBox "Your answer is NO in the security dialog" GoTo GoToNextSheet Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With [color=#000000]'Change all cells in the worksheet to values if you want[/color] If Destwb.Sheets(1).ProtectContents = False Then With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End If [color=#000000]'Save the new workbook and close it[/color] With Destwb .SaveAs FolderName _ & "\" & Destwb.Sheets(1).Name & FileExtStr, _ FileFormat:=FileFormatNum .Close False End With End IfGoToNextSheet: Next sh MsgBox "You can find the files in " & FolderName With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End WithEnd Sub [/code]
Hola,
Me gustaria que me ayudaran. Tengo esta macro de Ron de Bruin, que coge cada hoja de un libro y crea un libro nuevo con cada una de ellas sin formulas, me gustaria que hiciera lo mismo pero en un solo libro.
Salu2 y gracias.
multicol.xls
multicol.xls