Hey, I have about 20 different sheets from which I need to select 5 row
out of the middle, about 15 columns long and put it into a ne
sheet...Anybody have any idea how to get this into VBA so I dont hav
to copy and paste it all, I also want to make sure that when I go t
paste it into the new sheet, it makes sure that it comes to an empt
box before it pastes, so that I dont lose any data.
Any help appreciated. - Be

--
bm446
-----------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...fo&userid=3394
View this thread: http://www.excelforum.com/showthread.php?threadid=53724

Assume you only have worksheets in the workbook and you want to process al
the current worksheets to a newly added sheet.

Sub CopyData()
Dim k as Long , j as Long, i as Long
k = worksheets.count
worksheets.add After:=worksheets(k)
j = 1
for i = 1 to k
worksheets(i).Range("B21:O25").copy _
worksheets(k).Cells(1,j)
j = j + 5
Next
End Sub

--
Regards,
Tom Ogilvy

"bm4466" wrote:


Hey, I have about 20 different sheets from which I need to select 5 rows
out of the middle, about 15 columns long and put it into a new
sheet...Anybody have any idea how to get this into VBA so I dont have
to copy and paste it all, I also want to make sure that when I go to
paste it into the new sheet, it makes sure that it comes to an empty
box before it pastes, so that I dont lose any data.
Any help appreciated. - Ben


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=537244

Hey Tom,
First, thank you very much. Shouldnt I keep two variables though.
I see that J is incremented, but it seems as though I am always pasting
at (1,j) Is one the column? I thought it should be (j,j+5), but I am
really not sure.
Thanks, Ben


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=537244

Actually, there were two problems with the code.

Sub CopyData()
Dim k As Long, j As Long, i As Long
k = Worksheets.Count
Worksheets.Add After:=Worksheets(k)
j = 1
For i = 1 To k
Worksheets(i).Range("B21:O25").Copy _
Worksheets(k + 1).Cells(j, 1)
j = j + 5
Next
End Sub

was tested and worked for me.


You could actually do away with J and just use I alone:

Sub CopyData()
Dim k As Long, j As Long, i As Long
k = Worksheets.Count
Worksheets.Add After:=Worksheets(k)

For i = 1 To k
Worksheets(i).Range("B21:O25").Copy _
Worksheets(k + 1).Cells((i - 1) * 5 + 1, 1)
Next
End Sub

--
Regards,
Tom Ogilvy



"bm4466" wrote:


Hey Tom,
First, thank you very much. Shouldnt I keep two variables though.
I see that J is incremented, but it seems as though I am always pasting
at (1,j) Is one the column? I thought it should be (j,j+5), but I am
really not sure.
Thanks, Ben


--
bm4466
------------------------------------------------------------------------
bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
View this thread: http://www.excelforum.com/showthread...hreadid=537244