Question to keep Formatting to another WB via VBA.

[trixzy]

Okay, I've been searching all morning and I'm having trouble getting this code to work:

Sub CommandButton1_Click()MyBook = ActiveWorkbook.Name ' Retrieves the name of original WB.
Columns("A:A").AutoFit
Columns("B:B").AutoFit
Columns("C:C").AutoFit
Columns("D:D").AutoFit
Range("A5:D500").Select
Selection.Copy
Workbooks.Add ' Opens my New Workbook
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1").Select
ActiveSheet.PasteSpecial
ActiveWorkbook.SaveAs Filename:="C:\........" ' Save name of new book
Workbooks(MyBook).Activate ' Back to the original WB
Application.CutCopyMode = False
End Sub

Okay so this code works for me as it should opening a new WB pasting the info and prompting me to save the file.

The only issue here is that from the first WB I have AutoFit the columns A-D and this Formatting does not come over to the next workbook.

I've tried;

[COLOR=#000000][FONT=Segoe UI]Range("A1").PasteSpecial Paste:=xlFormats
[/FONT][/COLOR]ActiveSheet.PasteSpecial Paste:=xlFormats
Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlValues

and multiple other codes but I can't get the Column width to transfer over to the new workbook without breaking the paste function itself.

 

[Fluff]

How about simply adding

Range("A:D").EntireColumn.AutoFit

to the end of your code?

 

[trixzy]

How about simply adding

Range("A:D").EntireColumn.AutoFit

to the end of your code?

Doing this only affects the original workbook, not the new one that I open, paste the info in and save.

 

[Fluff]

Helps if I look at your code! It needs to go here

Sub CommandButton1_Click()
MyBook = ActiveWorkbook.Name ' Retrieves the name of original WB.
Columns("A:A").AutoFit
Columns("B:B").AutoFit
Columns("C:C").AutoFit
Columns("D:D").AutoFit
Range("A5:D500").Select
Selection.Copy
Workbooks.Add ' Opens my New Workbook
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1").Select
ActiveSheet.PasteSpecial
[COLOR=#ff0000]Range("A:D").EntireColumn.AutoFit[/COLOR]
ActiveWorkbook.SaveAs Filename:="C:\........" ' Save name of new book
Workbooks(MyBook).Activate ' Back to the original WB
Application.CutCopyMode = False
End Sub

 

[trixzy]

Thanks for your quick reply.
I tried that but the original issue still stands. It copies everything but doesn't still AutoFit on the new workbook it saves. The code does AutoFit the original document though.

 

[Fluff]

Apologies, it should be

ActiveSheet.Range("A:D").EntireColumn.AutoFit

 

[trixzy]

Okay I think I found the error. I had the macro done from ActiveX Control instead of Form Control and when I made a new button from "Form Control" the Form values are copied to the new workbook! Thank you for your help! =)

 

[Fluff]

Glad you got it sorted & thanks for the feedback