VBA code needed for excel export

Hi experts,

Here's what I'm trying to do :

1.

From an excel file (workbook1), create a new one without saving yet (workbook2)

2.

copy/paste only some excel sheets from workbook1 to workbook 2 (reference by name)

3.

for each sheet being copied, I need to paste values and format only for all rows and columns (I need to get rid of formulas)

4.

in some particular sheets (not all), I need to to remove some columns completely before copying to workbook2 (so the # of columns of workbook2 < workbook1)

5.

Prompt the Save As of workbook2 (assuming no changes were done to workbook1)
What would be the code to accomplish that?

Thanks
STIWasabiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Since you gave somewhat generic instructions, I wrote the macro in segments and trust that you can make the necessary changes. The "moving parts" are commented.
Sub WorkbookMangler()
'From an excel file (workbook1), create a new one without saving yet (workbook2)
'copy/paste only some excel sheets from workbook1 to workbook 2 (reference by name)
'for each sheet being copied, I need to paste values and format only for all rows and columns (I need to get rid of formulas)
'in some particular sheets (not all), I need to to remove some columns completely before copying to workbook2 (so the # of columns of workbook2 < workbook1)
'Prompt the Save As of workbook2 (assuming no changes were done to workbook1)
Dim wb1 As Workbook, wb2 As Workbook
Dim vSheet As Variant, vSheets As Variant

Set wb1 = ThisWorkbook

    'Create a new workbook
    'Copy/paste certain sheets from wb1. This creates a new workbook.
Application.ScreenUpdating = False
vSheets = Array("Sheet2", "Sheet4", "Sheet6") 'These worksheets get copied into workbook wb2
wb1.Worksheets(vSheets).Copy
Set wb2 = ActiveWorkbook

    'Paste values and format only
For Each vSheet In vSheets
    wb1.Worksheets(vSheet).Cells.Copy
    wb2.Worksheets(vSheet).Cells.PasteSpecial xlPasteValues
Next

    'Delete certain columns completely from specified worksheets
With wb2.Worksheets("Sheet2")
    .Columns("F").Delete    'Make sure you work from right to left when deleting!
    .Columns("C").Delete
End With

    'Display a Save As dialog for workbook wb2 and save it
Application.ScreenUpdating = True
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Open in new window

0
STIWasabiAuthor Commented:
I get an error on this line :
wb2.Worksheets(vSheet).Cells.PasteSpecial xlPasteValues

Run-time error '1004':
PasteSpecial method of Range class failed

Now, I do have several merged cells and unmerging is not an option. Any ways around this?

Can I use the below code in order to delete columns (range of columns as opposed of each column one by one) - yes I have quite a few to delete !
With wb2.Worksheets("TEST")
    .Columns("v:XFD").Delete
    .Columns("O:S").Delete
    .Columns("D:M").Delete
End With

Thanks
0
byundtMechanical EngineerCommented:
Yes, you can delete columns using a range like you showed for worksheet TEST. I've incorporated that tweak plus a fix for merged cells in the snippet below.
Sub WorkbookMangler()
'From an excel file (workbook1), create a new one without saving yet (workbook2)
'copy/paste only some excel sheets from workbook1 to workbook 2 (reference by name)
'for each sheet being copied, I need to paste values and format only for all rows and columns (I need to get rid of formulas)
'in some particular sheets (not all), I need to to remove some columns completely before copying to workbook2 (so the # of columns of workbook2 < workbook1)
'Prompt the Save As of workbook2 (assuming no changes were done to workbook1)
Dim wb1 As Workbook, wb2 As Workbook
Dim vSheet As Variant, vSheets As Variant

Set wb1 = ThisWorkbook

    'Create a new workbook
    'Copy/paste certain sheets from wb1. This creates a new workbook.
Application.ScreenUpdating = False
vSheets = Array("TEST", "Sheet4", "Sheet6") 'These worksheets get copied into workbook wb2
wb1.Worksheets(vSheets).Copy
Set wb2 = ActiveWorkbook

    'Paste values and format only
For Each vSheet In vSheets
    wb2.Worksheets(vSheet).UsedRange.Value = wb1.Worksheets(vSheet).UsedRange.Value
Next

    'Delete certain columns completely from specified worksheets
With wb2.Worksheets("TEST")
    .Columns("V:XFD").Delete    'Make sure that you delete from right to left. You can delete single columns or range.
    .Columns("O:S").Delete
    .Columns("D:M").Delete
End With

    'Display a Save As dialog for workbook wb2 and save it
Application.ScreenUpdating = True
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
STIWasabiAuthor Commented:
Pefect, thanks !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.