How to copy data from every 100th row?!
 

I have a spreadsheet (2003) that every month imports data from a weather
station €“ a sheet for each month. There are about 50,000 rows of data for
each month with 17 columns of data €“ temperature, wind speed etc.
What would like to do is have a macro start at say F2 €“ copy that data then
paste to say R1 €“ go back to F2 and move down say 100 rows and copy then
paste to R2 etc.
Any suggestions?!
Appreciate your help.
With thanks

How to copy data from every 100th row?!
 

Macros are not needed. In R1 enter:
=F2
In R2 enter:
=INDIRECT("F"&100*(ROW()-1)+2) and copy down

this will get you data from F2, F102, F202, etc.
--
Gary''s Student - gsnu200759


"Jim" wrote:

I have a spreadsheet (2003) that every month imports data from a weather
station €“ a sheet for each month. There are about 50,000 rows of data for
each month with 17 columns of data €“ temperature, wind speed etc.
What would like to do is have a macro start at say F2 €“ copy that data then
paste to say R1 €“ go back to F2 and move down say 100 rows and copy then
paste to R2 etc.
Any suggestions?!
Appreciate your help.
With thanks

How to copy data from every 100th row?!
 

You can do it without a macro using


R1: =INDEX(F:F, (ROW()-1)*100+2)

copying down as far as required.

Or you could use a macro:

Public Sub FtoRby100s()
Dim i As Long
For i = 1 To (Cells(Rows.Count, 6).End(xlUp).Row - 1) / 100 + 1
Cells(i, 18).Value = Cells((i - 1) * 100 + 2, 6).Value
Next i
End Sub


In article ,
Jim wrote:

I have a spreadsheet (2003) that every month imports data from a weather
station €“ a sheet for each month. There are about 50,000 rows of data for
each month with 17 columns of data €“ temperature, wind speed etc.
What would like to do is have a macro start at say F2 €“ copy that data then
paste to say R1 €“ go back to F2 and move down say 100 rows and copy then
paste to R2 etc.
Any suggestions?!
Appreciate your help.
With thanks

How to copy data from every 100th row?!
 

hi
i'm confused. do you want just the data in the F column or all the data in
the row from f over to q?

Regards
FSt1

"Jim" wrote:

I have a spreadsheet (2003) that every month imports data from a weather
station €“ a sheet for each month. There are about 50,000 rows of data for
each month with 17 columns of data €“ temperature, wind speed etc.
What would like to do is have a macro start at say F2 €“ copy that data then
paste to say R1 €“ go back to F2 and move down say 100 rows and copy then
paste to R2 etc.
Any suggestions?!
Appreciate your help.
With thanks

How to copy data from every 100th row?!
 

Hi there - just the data in F column.
Thanks

"FSt1" wrote:

hi
i'm confused. do you want just the data in the F column or all the data in
the row from f over to q?

Regards
FSt1

"Jim" wrote:

I have a spreadsheet (2003) that every month imports data from a weather
station €“ a sheet for each month. There are about 50,000 rows of data for
each month with 17 columns of data €“ temperature, wind speed etc.
What would like to do is have a macro start at say F2 €“ copy that data then
paste to say R1 €“ go back to F2 and move down say 100 rows and copy then
paste to R2 etc.
Any suggestions?!
Appreciate your help.
With thanks

How to copy data from every 100th row?!
 

hi.
good. you were saying "rows". I took that to mean more than 1 column.
then you should be fixed up by JE and/or Gary

regards
FSt1

"Jim" wrote:

Hi there - just the data in F column.
Thanks

"FSt1" wrote:

hi
i'm confused. do you want just the data in the F column or all the data in
the row from f over to q?

Regards
FSt1

"Jim" wrote:

I have a spreadsheet (2003) that every month imports data from a weather
station €“ a sheet for each month. There are about 50,000 rows of data for
each month with 17 columns of data €“ temperature, wind speed etc.
What would like to do is have a macro start at say F2 €“ copy that data then
paste to say R1 €“ go back to F2 and move down say 100 rows and copy then
paste to R2 etc.
Any suggestions?!
Appreciate your help.
With thanks