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

craig schultz

New Member
Joined
Dec 4, 2014
Messages
47
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
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
 
Upvote 0
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)
HbBPYKs.jpg
HbBPYKs.jpg


If you look at your VBA macro code you'll see a line like this :
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top