Copying Book1 to Book2 without selecting

[Guest]

Hi everyone

I require help to copy 21 rows of data (Value and Number Format only) from
Book1 Sheet1 to Book2 which contains 21 sheets.
What I’m trying to achieve is copy row2 in Book1 Sheet1 to the row2 in Book2
Sheet1.
Then copy row3 Book1 Sheet1 to row2 of Book2 Sheet2, continuing until all 21
rows are copied across.

Question 1. Can the above copying be achieved without actually selecting
Book2.?

The data is then changed in Book1 and the copying across process is repeated
again, placing the data on row2 of Book2. The data change and copying across
could happen 60 or more times.

The code I’m using at the moment is taking 8 or 9 minutes to run.
If involves coping all 21 rows across to sheet1 then Offsetting down 1 row
cutting and copying to next sheet. selecting Book 1 to change data and start
copy again.

Part of present code.

Dim wbk As Workbook
Set wbk = Workbooks("Book2.xls")
------
wbk.Activate
Sheets("Sheet1").Select
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValuesAndNumberFormats
‘
Application.CutCopyMode = False
‘
ActiveCell.Offset(1, 0).Range("A1:C20").Cut
'
Sheets("Sheet2").Select
Etc. etc. for 60 or more times.

Question 2. If copying without selecting Book 2 is possible would nested For
Next Statements outer one for data change, inner one for copying and pasting
be the way to achieve required outcomes?

Any help greatly appreciated

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

 

[Guest]

Hi everyone
Sorry there was a typo in first post, second run of copying should be pasted
on row3 not row2.

I require help to copy 21 rows of data (Value and Number Format only) from
Book1 Sheet1 to Book2 which contains 21 sheets.
What I’m trying to achieve is copy row2 in Book1 Sheet1 to the row2 in Book2
Sheet1.
Then copy row3 Book1 Sheet1 to row2 of Book2 Sheet2, continuing until all 21
rows are copied across.

Question 1. Can the above copying be achieved without actually selecting
Book2.?

The data is then changed in Book1 and the copying across process is repeated
again, placing the data on row2 of Book2. The data change and copying across
could happen 60 or more times.

The code I’m using at the moment is taking 8 or 9 minutes to run.
If involves coping all 21 rows across to sheet1 then Offsetting down 1 row
cutting and copying to next sheet. selecting Book 1 to change data and start
copy again.

Part of present code.

Dim wbk As Workbook
Set wbk = Workbooks("Book2.xls")
------
wbk.Activate
Sheets("Sheet1").Select
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValuesAndNumberFormats
‘
Application.CutCopyMode = False
‘
ActiveCell.Offset(1, 0).Range("A1:C20").Cut
'
Sheets("Sheet2").Select
Etc. etc. for 60 or more times.

Question 2. If copying without selecting Book 2 is possible would nested For
Next Statements outer one for data change, inner one for copying and pasting
be the way to achieve required outcomes?

Any help greatly appreciated