Merge two spreadsheets 2

[Clyde38]

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.

 

[IRstuff]

Huh?

Why not do a SaveAs to a new file?

Delete graph before you save?

TTFN

 

[bltseattle]

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.

 

[Clyde38]

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.

 

[bltseattle]

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

Sub copyvals()
'
'
    Range("A1:B18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book1").Activate
    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.

 

[Clyde38]

bltseattle, thanks. That works perfect. By the way, if you haven't looked at the utilities at

http://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.