I have a large Excel spreadsheet, that has been customized and augmented over a few years (with many worksheets), from which I'd like to extract specific data, and have that data inserted into a new spreadsheet following a strict format.
The second newer spreadsheet is a subset of the larger and older spreadsheet, however the data in the second spreadsheet must follow a strict format - no labels or headers, just data in columns and rows, starting with cell A1, and proceeding from there, with multiple worksheets as well.
How can I take data from specific cells in the original spreadsheet, and programmatically insert them into the second newer spreadsheet, via a macro or some other mechanism, such that I might simply click this "macro button" and the data will be transferred? The data from the older larger spreadsheet will not necessarily be an entire row or column (although it can be), but may also simply be a single cell that must be placed into a specific cell of the newer spreadsheet.
A very simple program where you can choose file to copy from and file to copy to as well as ranges is appended below. It provides you flexibility with simplicity. If you want to copy single cell just mention like "A10:A10"
Sub copyrange()
Dim w1 As Workbook
Dim w2 As Workbook
'## Open both workbooks first:
Set w1 = Workbooks.Open(" path to copying book ")
Set w2 = Workbooks.Open(" path to destination book ")
'Now, copy what you want from w1:
w1.Sheets("name of copying sheet").Range("X1:Y10").Copy
'Now, paste to w2 worksheet:
w2.Sheets("sheetname").Range("A2").PasteSpecial
'Close w1:
w1.Close
End Sub
I interpret your question to mean that you have a substantial number of cells and ranges in the source workbook to be moved to the destination workbook. Attempting to get hundreds of copy statements correct can be a very difficult task. There is also the problem that the owners of the workbook, who know what is to go where, have to document this for the programmer to implement. This increases the total amount of work and increases the opportunities for miscommunication.
A technique I have found successful is to have the copy macro in a third workbook. This third workbook contains a worksheet that specifies all the copies. For example
| A | B | C | D |
| ------ Source ----- | --- Destination --- |
| Worksheet | Range | Worksheet | Cell |
For each data row, columns A and B identify a range within the source workbook while columns C and D identify the top left cell of the range within the destination worksheet.
Merits of this technique include:
- Any Excel user can understand the format; it does not require any programming skills.
- If several users are involved in specifying the destination workbook, each can develop his/her section of the specification independently.
- Minimal code is required to “obey” this list of instructions.
The tasks to be performed by the code are:
- Open source workbook.
- Open empty destination workbook.
- Find last row of instructions.
- For each row of instructions: copy range defined by columns A and B to cell defined by columns C and D.
- Close destination workbook with save
- Close source workbook without save
Copies of the destination workbook can be passed to the owners for approval. Any mistakes will require updating of the instruction list and rerunning of the macro. It may take a number of cycles to get the desired result but each cycle is quite easy to manage.
The table above is the simplest I have ever implemented but more complicated versions are possible For example:
| A | B | C | D |
1| ------ Source ----- | --- Destination --- |
2| Worksheet | Range | Worksheet | Cell |
3|Sheet1 |A1:T230 |Data |A1 |
4|Sheet2 |A1:T150 |Data |A? |
5|Sheet1 |A1:T150 |Data |A1 |
6|Sheet2 |A1:X150 |Data |?1 |
In row 4, the question mark indicates that the destination is the next free row after the last copy. This is very convenient when several small tables are combined since it is not necessary to calculate which row is the next row. In row 6, the destination is the next free column.
Without a clear idea of the complexity of the rebuild, it impossible to suggest how far you should take this technique. You need to review all the types of copy and determine the easiest way of specifying those copies without making the programming too difficult.
