Merge two spreadsheets
Merge two spreadsheets
(OP)
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?
TTFN
RE: Merge two spreadsheets
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
RE: Merge two spreadsheets
CODE
'
'
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.
RE: Merge two spreadsheets