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

--
Susie
-----------------------------------------------------------------------
SusieQ's Profile: http://www.excelforum.com/member.php...fo&userid=3081
View this thread: http://www.excelforum.com/showthread.php?threadid=50637

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

--
Ian
--
"SusieQ" wrote in
message ...

Is there any way I can shorten the following code to copy all rows at
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

Thanks


--
SusieQ
------------------------------------------------------------------------
SusieQ's Profile:
http://www.excelforum.com/member.php...o&userid=30818
View this thread: http://www.excelforum.com/showthread...hreadid=506374

Brilliant! Thank yo

--
Susie
-----------------------------------------------------------------------
SusieQ's Profile: http://www.excelforum.com/member.php...fo&userid=3081
View this thread: http://www.excelforum.com/showthread.php?threadid=50637

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


Workbooks("copiedfrom.xls").Sheets("sheetcopiedfro m").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..

--
broro18
-----------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006
View this thread: http://www.excelforum.com/showthread.php?threadid=50637