4 réponses
Hello,
Here is the adapted code:
--
Best regards
Patrice
Here is the adapted code:
Option Explicit Sub test() Dim wbk As Workbook Dim shp As Shape ' Create a new workbook with a single sheet Set wbk = Workbooks.Add(xlWBATWorksheet) ' Copy the cells from the desired sheet (to be adapted) ThisWorkbook.Worksheets("Feuil1").Cells.Copy wbk.Worksheets(1).Cells ' Delete all shapes on the sheet For Each shp In wbk.Worksheets(1).Shapes shp.Delete Next shp End Sub --
Best regards
Patrice
We're almost there. :)
The code works, but the page format should remain the same (column widths, row heights, page size). When executing the code, the formats change. Everything needs to stay on a single page for printing. Also, my sheet contains an image that disappears when I run the code; it should remain, except for the buttons. But if it's not possible to keep the image, I can forgo the image without the buttons.
Thank you very much; your help is appreciated.
The code works, but the page format should remain the same (column widths, row heights, page size). When executing the code, the formats change. Everything needs to stay on a single page for printing. Also, my sheet contains an image that disappears when I run the code; it should remain, except for the buttons. But if it's not possible to keep the image, I can forgo the image without the buttons.
Thank you very much; your help is appreciated.
Hello
The formats remain the same (column widths, cell formats, conditional formatting, ...), what can change is the height of certain rows and the layout, which is not copied!
To preserve the image, you just need to know its name (it appears on the left in the formula bar when the image is selected), and you can copy the row heights:
--
Best regards
Patrice
The formats remain the same (column widths, cell formats, conditional formatting, ...), what can change is the height of certain rows and the layout, which is not copied!
To preserve the image, you just need to know its name (it appears on the left in the formula bar when the image is selected), and you can copy the row heights:
Option Explicit Sub test() Dim wbk As Workbook Dim wsh As Worksheet Dim shp As Shape Dim rng As Range Application.ScreenUpdating = False ' Set the source sheet Set wsh = ThisWorkbook.Worksheets("Feuil1") ' Create a blank workbook with a single sheet Set wbk = Workbooks.Add(xlWBATWorksheet) ' In sheet 1 of this workbook: With wbk.Worksheets(1) ' Copy the cells from the source sheet (to be adapted) wsh.Cells.Copy .Cells ' Copy the row heights For Each rng In .UsedRange.Rows rng.RowHeight = wsh.Rows(rng.Row).RowHeight Next rng ' Delete all shapes on the sheet, except Image 1 For Each shp In .Shapes If shp.Name <> "Image 1" Then shp.Delete Next shp End With Application.ScreenUpdating = True End Sub --
Best regards
Patrice
```vb
Option Explicit
Sub test()
Dim wbk As Workbook
Dim wsh As Worksheet
Dim shp As Shape
Dim rng As Range
Dim psu As PageSetup
Application.ScreenUpdating = False
' Définir la feuille source
Set wsh = ThisWorkbook.Worksheets("Feuil1")
' Créer un classeur vierge contenant une seule feuille
Set wbk = Workbooks.Add(xlWBATWorksheet)
' Sur la feuille 1 de ce classeur :
With wbk.Worksheets(1)
' Y copier les cellules de la feuille source (à adapter)
wsh.Cells.Copy .Cells
' Copier la hauteur des lignes
For Each rng In .UsedRange.Rows
rng.RowHeight = wsh.Rows(rng.Row).RowHeight
Next rng
' Supprimer toutes les formes posées sur la feuille, sauf Image 1
For Each shp In .Shapes
If shp.Name <> "Image 1" Then shp.Delete
Next shp
' Copier la mise en page (principales propriétés)
With .PageSetup
' Feuille
.Orientation = wsh.PageSetup.Orientation
.PaperSize = wsh.PageSetup.PaperSize
.PrintArea = wsh.PageSetup.PrintArea
' Marges
.TopMargin = wsh.PageSetup.TopMargin
.BottomMargin = wsh.PageSetup.BottomMargin
.RightMargin = wsh.PageSetup.RightMargin
.LeftMargin = wsh.PageSetup.LeftMargin
' En têtes et pieds de page
.RightFooter = wsh.PageSetup.RightFooter
.CenterFooter = wsh.PageSetup.CenterFooter
.LeftFooter = wsh.PageSetup.LeftFooter
.RightHeader = wsh.PageSetup.RightHeader
.CenterHeader = wsh.PageSetup.CenterHeader
.LeftHeader = wsh.PageSetup.LeftHeader
' Autres propriétés de mise en page ajoutées
.Zoom = wsh.PageSetup.Zoom
.FitToPagesWide = wsh.PageSetup.FitToPagesWide
.FitToPagesTall = wsh.PageSetup.FitToPagesTall
.CenterOnPage = wsh.PageSetup.CenterOnPage
End With
End With
Application.ScreenUpdating = True
End Sub
```
At the same time, I want to copy a sheet into a new workbook and not onto a second sheet.
This is so I can then send the latter by email as an attachment, in Excel format (because the recipient cannot open PDF files).
The purpose of this: By saving it in a new, unprotected workbook, we can delete the buttons contained in the original form that are useless to the recipient. (These buttons are initially meant to navigate from one sheet to another in a large workbook). Other people should be able to do this easily without needing the password to protect the main file.
Otherwise, I thought about emailing only the printable part of the sheet (using page break view and shading the part I don't want), but again, I don’t see how to do it. Phew. Thank you if someone can help me.
Thank you.