Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Merge two spreadsheets

Merge two spreadsheets

Merge two spreadsheets

I have software that outputs data (graphs and values) in a .xls format.  It uses the default "3 sheets" and the data and graphs are on the first two.  I have constructed enhanced graphs form the data on the first two sheets.  Right now, I "copy" the first two sheets and "paste values" (as not to copy the links for the graphs) to the new spreadsheet.  I'd like a more "refined" way to accomplish this.  Others will be using it.

RE: Merge two spreadsheets


Why not do a SaveAs to a new file?

Delete graph before you save?


RE: Merge two spreadsheets

If I understand, you are starting with a different file each time, the one generated by the other software that has 3 worksheets.  This sounds like a job for the Macro Recorder.

In general, You could try this:
1.  Open the "source" and "target" worksheets.
2.  Go to the "source" worksheet.  Open and start Excel's Macro Recorder from the Tools menu.
3.  Perform your operation, just like you always do and want to in the future.  After you copy/paste values, stop the macro recorder.

At this point you've recorded your operation. To run it, go to Tools>Macro.  You can add a custom button to a tool bar with your macro assigned to it.

4.  Open the visual basic editor and look at your macro code.  Correct the filenames if needed so they will work with your files in the future.

Good luck, let us know if you need more help.

RE: Merge two spreadsheets

Thanks IRstuff and bitseattle for your replies.  I should better define my situation.  I don't have access to the source code for application software that generates the data I use for my graphs.  The software that saves the data to a 3-sheet exel file allows me to name the file.  No options other than filename.  The enhanced graphing is in a separate spreadsheet.  Of course I can, and do provide a new filename for each file.  I think that the Macro approach will work fine.  Is there a way to be prompted for the filename while the Macro is running?  Or do I have to go to the editor each time and modify?  I am looking into an add-in from asap-utilities that claims to have a file import to merge one spread sheet with the other.

RE: Merge two spreadsheets

I did a little test on your behalf.  Recording a macro of cut & paste values from one workbook to another yields:


Sub copyvals()
    Application.CutCopyMode = False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Note that the source workbook name doesn't show up, the macro just starts on whatever worksheet you are on (with the active selected cell).  The easiest way to do your task is to have a "template" workbook that receives the copied data, then you "save-as" with a different name.  Then you would change "Book1" to "Template" in the code above, for example.

Easiest way would be to save the VBA macro into the "Template.xls" workbook.  

If "template" has more than one worksheet, and you are pasting the values onto sheet1, for instance, set the active cell to a different sheet before recording the macro.  This way the switch to the desired sheet will be recorded.

RE: Merge two spreadsheets

bltseattle, thanks.  That works perfect.  By the way, if you haven't looked at the utilities at www.asap-utilities.com, try it.  Quite a few useful utilities.  The import works well, but not for what I have already done.  Your macro aproach is what I was looking for.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close