Copying Multiple Rows

[SusieQ]

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

 

[Ian]

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

 

[SusieQ]

Brilliant! Thank yo

 

[broro183]

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

Workbooks(1).Activate
Range("E12:H12") = "VCCS"
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..