VBA code for copy paste special range

[pat01]

Hi
I need your help please? I have a workbook "Main" with sheet "Sheet 1". I need to use VBA (command button) to copy and paste special values columns A:Z to a new workbook . The command button should be in "Main" workbook sheet "Sheet 1"
Thanks in advance

 

[mohadin]

Hi
Ttry

Sub test()
    Dim a As Variant
    Dim wb As Workbook
    a = Cells(1, 1).CurrentRegion
    ReDim Preserve a(1 To UBound(a), 1 To 26)
    Set wb = Workbooks.Add
    wb.Sheets("sheet1").Cells(1, 1).Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

 

[pat01]

Hi
Ttry

Sub test()
    Dim a As Variant
    Dim wb As Workbook
    a = Cells(1, 1).CurrentRegion
    ReDim Preserve a(1 To UBound(a), 1 To 26)
    Set wb = Workbooks.Add
    wb.Sheets("sheet1").Cells(1, 1).Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

Hi
Ttry

Sub test()
    Dim a As Variant
    Dim wb As Workbook
    a = Cells(1, 1).CurrentRegion
    ReDim Preserve a(1 To UBound(a), 1 To 26)
    Set wb = Workbooks.Add
    wb.Sheets("sheet1").Cells(1, 1).Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

It worked thanks but only the format of the cells was not copied across . In the original workbook I have filters and some highlighted fields? Also, the width of the column should stay the same.
Thanks again in advance ?
Many thanks ?

 

[pat01]

Can we please add to the VBA code above the formatting of the cells. I need to copy also the same format of the cells that I had in my original workbook?
Thanks