Copying Multiple Rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SusieQ - 30 Jan 2006 15:41 GMT

Is there any way I can shorten the following code to copy all rows a
once?

Private Sub CommandButton1_Click()

If CheckBox1.Value = True Then

Workbooks.Open ("N:\Projects\Active Projects\Project sheets\VCCS.xls")
Worksheets(1).Rows(4).Select
Selection.Copy

Workbooks(1).Activate
Range("E12:H12") = "VCCS"
Worksheets(1).Rows(13).Select
ActiveSheet.Paste

Workbooks(2).Activate
Worksheets(1).Rows(5).Select
Selection.Copy
Workbooks(1).Activate
Worksheets(1).Rows(14).Select
ActiveSheet.Paste
Workbooks(2).Activate
Worksheets(1).Rows(6).Select
Selection.Copy
Workbooks(1).Activate
Worksheets(1).Rows(15).Select
ActiveSheet.Paste
Workbooks(2).Activate
Worksheets(1).Rows(7).Select
Selection.Copy
Workbooks(1).Activate
Worksheets(1).Rows(16).Select
ActiveSheet.Paste
Workbooks(2).Activate
Worksheets(1).Rows(8).Select
Selection.Copy
Workbooks(1).Activate
Worksheets(1).Rows(17).Select
ActiveSheet.Paste

End If

End Sub

Thank

Reply to this Message

Ian - 30 Jan 2006 16:38 GMT

Worksheets(1).Range("A4:A8").EntireRow.Select
to select multiple rows for copying/pasting.

Signature

Ian
--

>
> Is there any way I can shorten the following code to copy all rows at
[quoted text clipped - 43 lines]
>
> Thanks

Reply to this Message

SusieQ - 31 Jan 2006 09:12 GMT

Brilliant! Thank yo

Reply to this Message

broro183 - 31 Jan 2006 11:01 GMT

Hi Susie,
Here is a one liner that takes Ian's suggestion one step further:

Workbooks("copiedfrom.xls").Sheets("sheetcopiedfrom").Rows("4:8").Cop
_
Workbooks("copiedto.xls").Sheets("sheetcopiedto").Range("A26")

Just change the details as required within the "" 's. NB, this copie
formulae etc as they are but it looks like this is what your origina
code does now. (I'm sure the paste technique would be possible t
change, but I don't know the correct syntax.)

It doesn't cover your first few lines of code,

> Workbooks.Open ("N:\Projects\Active Projects\Projec

sheets\VCCS.xls")

> Worksheets(1).Rows(4).Select
> Selection.Copy
[quoted text clipped - 3 lines]
> Worksheets(1).Rows(13).Select
> ActiveSheet.Paste

but the pasting can be adapted using the above approach.

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

Reply to this Message