A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to cut & paste of multiple rows in many workbooks simultanousl



 
 
Thread Tools Display Modes
  #1  
Old July 22nd 07, 11:58 AM posted to microsoft.public.excel.misc
Narnimar
external usenet poster
 
Posts: 132
Default How to cut & paste of multiple rows in many workbooks simultanousl

I do data entries in a main workbook set of sheets with a table of jobs
progress status in the rows. Each row is meant for a particular job only.
Also I got 4 different Workbooks with many sheets in which some columns are
as same data as my sheet of the main work book. I enter the updates all at
once (by opening workbooks one by one) by linking particular first cell to
main book & dragging it in the columns of a particular sheet. Once my job
completed I cut that whole raw and paste it some where down the screen.
Question – Is it possible make a settings to enter them as well as cut &
paste simultaneously in other 4 books?

Ads
  #2  
Old July 22nd 07, 07:00 PM posted to microsoft.public.excel.misc
Stephane Quenson
external usenet poster
 
Posts: 53
Default How to cut & paste of multiple rows in many workbooks simultanousl

This can be done with a macro only, as far as I know. I assume that you have
already selected the rows to copy and that the four documents are open.

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

  #3  
Old July 22nd 07, 07:36 PM posted to microsoft.public.excel.misc
Narnimar
external usenet poster
 
Posts: 132
Default How to cut & paste of multiple rows in many workbooks simultan

Dear Stephane,
As the macro is required for this I could not do it. I am not used macros
yet. I may try if you can guide with step by step procedure. Thank you.

"Stephane Quenson" wrote:

> This can be done with a macro only, as far as I know. I assume that you have
> already selected the rows to copy and that the four documents are open.
>
> 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
>

  #4  
Old July 22nd 07, 09:36 PM posted to microsoft.public.excel.misc
Stephane Quenson
external usenet poster
 
Posts: 53
Default How to cut & paste of multiple rows in many workbooks simultan

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.

  #5  
Old July 23rd 07, 06:46 PM posted to microsoft.public.excel.misc
Narnimar
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.
>

  #6  
Old July 24th 07, 08:38 AM posted to microsoft.public.excel.misc
Stephane Quenson
external usenet poster
 
Posts: 53
Default How to cut & paste of multiple rows in many workbooks simultan

Sorry, I am not used to this silly forum application that adds line break
when you don't want to. "name accordingly" is not a line of code, it is the
continuation of the previous comment. Move it to the previous line and it
should work.

"Narnimar" wrote:

>
> 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.
> >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
insert rows and update linked cells in multiple workbooks [email protected] Excel Discussion (Misc queries) 1 April 2nd 07 10:37 PM
Macro to cut/paste from different workbooks Hawkdriver Excel Discussion (Misc queries) 1 December 25th 06 08:08 AM
Inserting rows in multiple workbooks 50pingviner Excel Discussion (Misc queries) 2 December 21st 05 04:15 PM
Copy and paste between workbooks Steve Boxler Excel Discussion (Misc queries) 0 August 25th 05 07:30 PM
Can't Copy and Paste or Paste Special between Excel Workbooks wllee Excel Discussion (Misc queries) 4 April 5th 05 07:51 PM


All times are GMT +1. The time now is 02:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.