VBA to copy Columns A-F into new work book keeping formatting

[craig schultz]

Hi, I wonder if anyone could help me.

I am trying to copy columns A-F into a new workbook and save new workbook in the current folder.

I currently have the code below which does what I want except it copies the whole sheet, not just my specified columns

Sub Customer Copy()

' Copy activesheet to the new workbook
ActiveSheet.Copy

'Save new workbook as MyWb.xls(m) into the folder where ThisWorkbook is stored
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\CustomerCopy", xlWorkbookNormal
MsgBox "It is saved as " & ActiveWorkbook.FullName & vbLf & "Press OK to close it"

' Close the saved copy
ActiveWorkbook.Close False

End Sub


I would be very grateful for any help,

Many thanks

Craig

 

[tdh777]

HI Craig,

1) Turn you macro recorder on (on the Developer Tab)
2) Do the copy operation yourself (ie select columns A to F, Copy and Paste them where you want to put them)
3) Press "Stop recording" on the Developer Tab.
4) Examine the resulting macro. Ylu will soon see how easy it is to copy just columns A-F in VBA instead of copying the whole of ActiveSheet.

If you are still stuck after that, then by all means ask further, but I'm pretty sure you'll see your own answer !

Cheers,

Warren K.

 

[craig schultz]

Hi Warren, Thanks for your reply.

I have done as you suggested above thank you and I have copied the code below. but it doesn't keep the same column widths and heights? Is there a way to copy these too?

Range("A1:F127").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Range("G14").Select
Windows("BD 962 Manufacturing pbom - With Total QTY - Copy.xlsm").Activate


Many thanks

Craig

 

[tdh777]

Hi Craig,

Yep there is. It's the same as when you do it yourself using Excel.

After you do the initial paste, you then do another Paste-Special selecting Column Widths : (right-click if necessary to get the Menu that shows Paste-Special)

If you look at your VBA macro code you'll see a line like this :

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

See how much you can learn simply by recording what you want to do ?

Cheers

 

[craig schultz]

Thanks very much for your help. Every days a school day!