Tricky Cut & Paste combo
Posted by Paul Allen on May 23, 2000 7:52 AM
I need help
please.
I need a macro that can "cut" information from 2 separate workbooks and paste onto a new workbook.
The complexity is that in both workbooks the data needed is in sections and the amount of data sections can vary .
However there is uniformity, each section of data is 10 rows in length (eg R6-R15) and 7 columns wide (eg A-G). There is also a space (1 row) between each section.
I need the Macro to start at the top of the 2 workbooks and find the first section of data cut and paste onto new workbook, then go back to the 2 workbooks and find the next section of data, again cut and paste onto new workbook - except paste onto a new sheet. Then to go back to the 2 workbooks, find the next section and cut and paste in new workbook on another sheet. This should go on until all data sections have been cut.
At the end there should be 3 workbooks, 2 with no data any more and 1 with all the data spread over multiple sheets.
PS the pasted data in new workbook needs to be spaced apart ( eg A5:G14 & A16:G25).
It's a tricky one and hard to explain. I hope it is clear anyone brave enough to take a stab at!
Cheers.
Paul Allen.
Posted by thomas venn on May 23, 2000 8:08 PM
You need to name (save as) your first workbook with data in it
"wb1.xls" and the second workbook with data in it
"wb2.xls". your third workbook where it copies to needs
needs to be named "WB3_New.xls". you can change all the
names later.
it may not be beautiful, but it is functional. give it a
try.
regards,
Thomas
ps - say hello to bill and steve for me.
Sub Macro1()
Application.Goto Reference:="R1C1"
Selection.End(xlDown).Select
ActiveCell.Range("A1:G10").Select
Selection.Cut
Windows("WB3_New.xls").Activate
Sheets.Add
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveSheet.Paste
Windows("wb2.xls").Activate
Application.Goto Reference:="R1C1"
Selection.End(xlDown).Select
ActiveCell.Range("A1:G10").Select
Selection.Cut
Windows("WB3_New.xls").Activate
ActiveCell.Offset(11, 0).Range("A1").Select
ActiveSheet.Paste
Windows("wb1.xls").Activate
Application.Goto Reference:="R1C1"
Selection.End(xlDown).Select
ActiveCell.Range("A1:G10").Select
Selection.Cut
Windows("WB3_New.xls").Activate
Sheets.Add
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveSheet.Paste
Windows("wb2.xls").Activate
Application.Goto Reference:="R1C1"
Selection.End(xlDown).Select
ActiveCell.Range("A1:G10").Select
Selection.Cut
Windows("WB3_New.xls").Activate
ActiveCell.Offset(11, 0).Range("A1").Select
ActiveSheet.Paste
Windows("wb1.xls").Activate
Application.Goto Reference:="R1C1"
Application.Run "wb1.xls!Macro1"
Application.Run "wb1.xls!Macro1"
End Sub
Posted by thomas venn on May 23, 2000 8:17 PM
oh yea, forgot, the following needs to be changed:
change this
Application.Run "wb1.xls!Macro1"
Application.Run "wb1.xls!Macro1"
to this:
Application.Run "Macro1"
Application.Run "Macro1"
*****
also, you need to have your cursor at the worbook
"wb1.xls" in cell a1
cheers,
thomas
Posted by Celia on May 23, 2000 11:23 PM
Thomas
But what about the following situations :-
-There is a set of data in wb1 or wb2 that happens to have no data in column A ?
-A set of data starts at cell A1 ?
-There is some content in the cells in column A above where the first set starts ?
Celia
Posted by Paul Allen on May 24, 2000 2:52 AM
Cheers guys.
This Macro was excellent.
One thing. Is there a way to stop the "Run-time error 1004", "Apllication-defined or object-defined error"
When you debug it goes to ActiveCell.Range("A1:G10").Select
Any thoughts?
Thanks again, Paul.
(Bill's at bit perplexed by the court hearing, he sends his love).
Posted by thomas venn on May 24, 2000 9:01 AM
hi Celia,
i did not take those factors into consideration. i assumed that both wb1 and wb2 would have the same amount of data. the way the data is described, it seems like it came off of a database by doing a "print screen" then convert to excel.
However, if you can think of a way to adjust for the variables, please feel free to manipulate the code. it would be nice for the macro to end by itself when all the data is gone. currently, it ends by "error" when it can no longer find any data in wb1, such that wb2 may still contain data, but the macro will not be able to go to wb2 once all the data is gone in wb1.
regards,
thomas
