copy a worksheet to new workbook with filename linked to cell

[pika]

I admit I am not good with VBA at all, but have pieced together some code from reading other forum threads. I would appreciate any help or suggestions anyone may have!!!

I have a Command Button on Worksheet 1 of Workbook 1. When pressed, I would like for it to copy the entirety of Worksheet 2 (as values since it contains links to pivot reports) to a new Workbook (eg Workbook 2). I would like for the file name and path to be linked to user entered text in cell U11 in Worksheet 1.

This is my code so far:

Please Login or Register  to view this content.

[alansidman]

It would be helpful if you uploaded a sample spreadsheet with data so that we can see the entire layout of what you are attempting to copy and paste. Right now there are several compile errors in your code and it appears that you are first deleting column A and then copying column A to a new spreadsheet. So its not making much sense right now.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pika]

Thanks for the response Alan, I will get a sample sheet uploaded shortly

[AlphaFrog]

Give something like this a try.

Please Login or Register  to view this content.

[pika]

Okay, I've uploaded a mock-up of my workbook. (I think I did this properly, let me know if you cannot access the file). I've also outlined my goals for the end product below.

1.) The user selects various parameters via slicers on "GUI" sheet. Number of cells/columns in "Worksheet 1" are dynamic based on user selection.
2.) The user enters pathname and filename into cell U11 on "GUI" sheet.
2.) Entirety of Worksheet 1 is copied and pasted into new workbook with path name and file name equal to entered value in cell "GUI" sheet, cell "U11"

Thanks so much in advance for your time and help!

Excel Forum Sample Doc.xlsm

[pika]

Thanks for all of your help AlphaFrog!

I just tried out your code substituting appropriate worksheet names and I am getting a "Run-time error '1004' PasteSpecial method of Range class failed" error at the line ".PasteSpecial x1PasteColumnWidths." Do you have any insight as to why this may be happening?

[AlphaFrog]

Did you change the L to a 1 ?
x1PasteColumnWidths

Should be...
xlPasteColumnWidths

Otherwise, show your modified code.

[pika]

Hi again,

Yes, the l's are now 1's

Please Login or Register  to view this content.

[AlphaFrog]

No. They should be Ls and not ones.

The original code I had posted has Ls which is correct.

You stated it errored...

... error at the line ".PasteSpecial x1PasteColumnWidths." Do you have any insight as to why this may be happening?

... and quoted it with a one which is incorrect. I was asking if you had incorrectly inserted a one where it should be an L.

[pika]

I transcribed it incorrectly when I was modifying the code...the secondary workbook is saving properly now.

Thank you so much for all of your help AlphaFrog!!!