Copy data from one worksheet to another.. but not contiguous data

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have searched through everything looking for a simple answer to what
seems a simple task.. but I have not been lucky thus far...

Worksheet1
Column G
Row 1-14 nothing needed
Row 5 Data #1
Row 10 Data #2
Row 15 Data #3
....
Row 2200 Data ...

I am trying to take data from worksheet #1 starting at G5 then every 5
cells until row 2200 and put it in a seperate Worksheet in order...

Worksheet2
Column C
Row 1-10 nothing pasted
Row 11 Data #1
Row 12 Data #2
Row 13 Data #3
....
Row 370 Data #...

I could do it by hand, but it is a pain to do 370 entries. I have
been trying with Indirect and Offset... I don't want to use an INDEX.

Any ideas?

Thanks.
 
I'd use a macro such as the following:

Sub CopyPaste()

Dim x, y As Integer

x = 5
y = 11

While Not IsEmpty(Worksheets("Sheet1").Cells(x, 7).Value)
Worksheets("Sheet2").Cells(y, 3).Value =
Worksheets("Sheet1").Cells(x, 7).Value
x = x + 5
y = y + 1
Wend

End Sub

This macro starts at G5 in Sheet1 and makes C11 in Sheet2 the same value.
The macro continues, increasing the row in Sheet1 by 5 (to get every 5th
item) and increases the row in Sheet2 by 1 (to get a continuous list), until
the row value in Sheet1 is empty (has nothing in it).

HTH
 
Yes, it was a bit of a typo..

Data in Row 1-14 is not used. Should have started the remaining data
at 15/20/25 instead of 5/10/15...

Secondly, I would like to change data in the first sheet. Meaning, I
will cut and paste different data (to the same locations) on sheet1,
and would like sheet2 to automatically change to suit. So I need
references not copied data, sorry for not stating that in my first
post.

I know I could have done this by hand by now, but I do this
occasionally and would like to learn how ! :)
 
First, I like to keep my data in one spot and split it up later. If I screw
something up--delete a row, insert a row, my formulas may go wacky.

If you buy into that, then even if I were doing it in a macro, I'd still use the
helper column. But this time my formula would be:

=IF(ROW()<15,"",IF(MOD(ROW(),5)=0,"keep",""))

Then I'd apply data|filter|autofilter and just show the Keep's.

Copy the visible cells and paste to whereever I wanted.

If I needed a macro, I'd start by recording it once when I did it by hand.

(Then when/if I got stuck, I post back with a snippet of code or a specific
question.)
 
Back
Top