Create macro to copy-n-paste all data from an existing worksheet to another existing works

**jdunbarjr** · 11-02-2013, 04:43 PM

I am back again. I need more help. (By the way, I love this site). I need help with something I'm sure if very simple to many of you. I need to create a macro to copy a selection of data from an existing excel spreadsheet and copy it over to a new excel workbook keeping all of the same formatting (including column widths, etc.). I have most of it when I "recorded" a macro that simply made a copy of the spreadsheet and then moved it into a new workbook, which on the most part works, but the new worksheet has vlookups and command buttons on it that I do not need. I just the selected area that contains the data. I then recorded the macro to copy and special paste to remove the vlookup links, which I believe works, then I selected the data that I do not want and deleted it and I had to go into the Developer tab and activate "Design Mode" so I could delete the two (2) command buttons, then deactive Design Mode. That is pretty much how I recorded my macro. When I run it, it mostly works--it copies the entire page over to a new workbook, I believe it does the special paste for the selected data area that I want to keep, but then I get an "ERROR 1004" where it will not delete the selected area that I do not want nor the command buttons. I am sure it is something very simple that I am missing. It actually seems that this ERROR 1004 is coming up in the new workbook that is created, possibly because of the two command buttons that are being moved to the new workbook.

Here is the code that I have so far.

Please Login or Register  to view this content.

**xladept** · 11-02-2013, 05:26 PM

Hi J,

Try this:

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**jdunbarjr** · 11-02-2013, 05:50 PM

Most of it worked. I tweeked it a bit so it only copies A1:H60 and paste in the same range in the new workbook. There are no ERRORS that come up, which is great. However, the only thing now is that the formatting is not copying over to the new workbook. It is just the data in raw format. And a picture that I have within that range A1:H60, which is my companies LOGO is not copying over. Is there a way that the LOGO picture can copy over and also keep the exact same formatting (column widths, colors, etc.)?

**xladept** · 11-02-2013, 06:57 PM

Hey J -put code tags around your code please.

Maybe:

Please Login or Register  to view this content.

**jdunbarjr** · 11-04-2013, 09:12 AM

Oh I did not know about the "tags" (#), I will remember that moving forward. The new changed you provided is almost there. Now, my company logo picture does copy over. However, the only missing formatting now is the column widths, but more importantly, none of the data copies over now, which is pulled using vlookups--those data fields all say "#N/A" when copying over.

**jdunbarjr** · 11-04-2013, 11:30 AM

I hope I was able to comply with the rules now; I think I did.
The new changed you provided is almost there. Now, my company logo picture does copy over--thanks. However, the only missing formatting wise now is the column widths, but more importantly, none of the fields copies over now with the actually data, which is pulls from using vlookups. Now, all those data fields say "#N/A" when copying over.

**xladept** · 11-04-2013, 02:27 PM

Maybe:

Please Login or Register  to view this content.

**jdunbarjr** · 11-04-2013, 02:56 PM

oooh oooh almost there.

Now, all the values are copying over. The only thing now is the column widths are not coming through. Everything else is perfect now.

**xladept** · 11-04-2013, 03:36 PM

Let's see if it will take another pasting:

Please Login or Register  to view this content.

**jdunbarjr** · 11-04-2013, 03:57 PM

With all of your help and a couple of changes I just made, we got it to work perfectly now. THANKS SO MUCH!!! AWESOME HELP -- as always !!!

Here is the final coding:

Please Login or Register  to view this content.

**xladept** · 11-04-2013, 04:16 PM

You're welcome!

- (you could use:

Please Login or Register  to view this content.

)

**jdunbarjr** · 11-04-2013, 04:32 PM

actually, I just noticed one additional thing that is weird, with the actual command button caption. Every time I clicked this command button, the caption shrunk and now is basically disappeared. I checked the properties and it does show "GENERATE PO (.xls)" in that field, but is no longer showing up on the command button. Has anyone heard of anything like this?

Create macro to copy-n-paste all data from an existing worksheet to another existing works

LinkBack

Thread Tools

Rate This Thread

Display

Create macro to copy-n-paste all data from an existing worksheet to another existing works

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Re: Create macro to copy-n-paste all data from an existing worksheet to another existing w

Thread Information

Users Browsing this Thread

Similar Threads

Create macro to copy-n-paste all data from an existing worksheet to another existing works

VBA to copy, paste and add to existing data

Macro to copy row into existing worksheet when data entered

Macro Copy/Save active worksheet into a pre-existing workbook & rename sheet to cell data

[SOLVED] copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro

Bookmarks

Bookmarks

Posting Permissions