View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Narnimar Narnimar is offline
external usenet poster
 
Posts: 132
Default How to cut & paste of multiple rows in many workbooks simultan


I have copied the contents below to try with two books, but got syntax
error. The text ''name accordingly'' becomes red. "Sub
CopySelectionToFourDocuments()" yellow highlighted.

Sub CopySelectionToFourDocuments()

' Copy the four set of lines four times and change the file name and sheet
name accordingly.
' ====== Start Copy the line below =======
Selection.Copy
Windows("Book2.xls").Activate ' Activate the receiving document
Sheets("Sheet2").Select ' Select the proper sheet
Application.Goto Reference:="R65536C1" ' Go to the last cell in column A
Selection.End(xlUp).Select ' Go up to find the first non-empty cell
Cells(Selection.Row + 1, Selection.Column).Select
ActiveSheet.Paste
' ====== Stop copy the line above this one ======

End Sub

Also please note my both files are in 2007 comptability mode



"Stephane Quenson" wrote:

1. Make sure that you have only 1 excel file open, the one with the rows to
copy from.

2. Press Alt-F11 to open the macro editor (or Menu Tools Macro Visual
Basic Editor).

3. You should have a left pane called "Project - VBA project", double click
on Sheet1 (or the first sheet you see on that pane). The right pane is
changing but it should be empty.

4. Copy the whole code of my previous post, starting from the line
Sub CopySelectionToFourDocuments(), to the line
End Sub

5. Change on the line Window the file name, and replace it with your first
file that needs to receive the records. Do the same on the line below and put
the sheet name. The macro assumes that column A contains data, and it will
use this column to find the first empty row. If you prefer to use another
column, just change the text R65536C1 by R65536C2 for column B, R65536C3 for
column C etc (only the last digit is changing).

6. Open the document that will receive the records (same name as of step 5).

7. Go back to the main document, select some rows, and press Alt-F8 (or menu
Tools Macro Run macros). You should see one macro called
CopySelectionToFourDocuments. Select it and click on the button Run.

8. If everything is OK, the selected rows have been copied to the second
document, after the last row containing data in column A.

9. Go back to the macro editor, and copy the lines between the ==== three
times. Change the file names and sheet names, and eventually column name,
using the same recommendations as step 5.

If you see an error message or anything strange, let us know!

Stephane.