+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

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

    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.
    Last edited by jdunbarjr; 11-04-2013 at 11:24 AM. Reason: adding code tags

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    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
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

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

    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.)?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Hey J -put code tags around your code please.

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

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

    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.

  6. #6
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

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

    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.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Maybe:

    Please Login or Register  to view this content.
    Last edited by xladept; 11-04-2013 at 02:38 PM.

  8. #8
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

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

    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.

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Let's see if it will take another pasting:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

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

    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.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    You're welcome! - (you could use:
    Please Login or Register  to view this content.
    )

  12. #12
    Registered User
    Join Date
    09-21-2013
    Location
    Florida-USA
    MS-Off Ver
    Excel 2007
    Posts
    18

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

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 10-14-2013, 03:06 PM
  2. VBA to copy, paste and add to existing data
    By Jim885 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2013, 05:58 PM
  3. Macro to copy row into existing worksheet when data entered
    By Backroads23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2012, 02:31 AM
  4. Replies: 0
    Last Post: 04-28-2012, 06:06 PM
  5. Replies: 1
    Last Post: 10-17-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1