Copying Non-Adjacent Values of a Worksheet to Another

  • Thread starter Thread starter waltzm
  • Start date Start date
W

waltzm

Hello

Please help me with this. I am trying to copy data in cells in one
worksheet to another. The data is such that it is located in, for
example, cells A1, A5, A9, A13, etc (i.e. 4 cells down) and copying it
to another worksheet such that it is adjacent i.e. to A1, A2, A3, A4,
etc. I need a reference or formula for such.

My problem is the data is huge - about 5000 rows. So I cannot copy and
paste each entry.

I hope there is something for this kind of operation. Excuse me, I'm a
novice sometimes with Excel.

Regards
 
Someone who "does code" could doubtless give you a code-based solution.
However, you might be able to use formulas.

In the first free column to the right, enter

=MOD(ROW(),4)

Drag this down to the bottom, then apply an AutoFilter where this field = 1.

Select all your data bar the formula column (click & drag the column
headers) and copy. Paste into your other sheet & you should get just the
filtered rows. Takes 1-2 minutes.

HTH,
Andy
 
Thank you Andy

Unfortunately the formula does not seem to work, it just gives m
numbers (2, 3, 4) but not the actual contents of the preceding column.

Hope someone would also help with the 'code mode'.

Maybe I did something wrong tho, see if you can identify it or mayb
suggest a different formula if possible.


Regard
 
Maybe I did something wrong tho

Probably, since I didn't get anything like that. Tho' I expect I explained
it crap.

Since no-one else offered, try this (warning ; save your data first, my
coding efforts completely suck).

ALT+F11 to open the VBE, CTRL+R for the Project Explorer. Make sure "VBA
Project (yourfilename)" is highlighted and Insert a Module. Paste in the
following (don't forget to edit the sheet names in the code) ;

----------------------------------------------------------------------------
-----
Sub Get_Fourths()
'Dim Me As VBA illiterate
Range("Sheet2!A1") = Range("Sheet1!A1")
x = WorksheetFunction.CountA(Range("A:A"))
n = 4
Do Until n > x
ActiveCell.Offset(n, 0).Copy Range("Sheet2!A65536").End(xlUp).Offset(1, 0)
n = n + 4
Loop
End Sub
----------------------------------------------------------------------------
--------

ALT+Q to switch back to Excel. Select A1 on "Sheet1" and run it via ALT+F8.

HTH,
Andy
 
Thanx again Andy for your efforts.

This seems to be on the right track but hasnt actually given me th
results I wanted. What I had given you was an example, so I had to edi
the 'code' u gave me.

The worksheets I am working on are: WDI data (original worksheet) an
Reconciled data (where I want to copy the data to) and the cells are o
cloumn C and are 7 cells (down) from each other. (The first 2 rows an
first two columns are occupied by variable definitions, etc).

Thus I edited your code to this:

Sub Get_Fourths()
'Dim Me As VBA illiterate
Range("Reconciled Data!C3") = Range("WDI data!C3")
x = WorksheetFunction.CountA(Range("C:C"))
n = 7
Do Until n > x
ActiveCell.Offset(n, 0).Copy Range("Reconcile
Data!C65536").End(xlUp).Offset(1, 0)
n = n + 7
Loop
End Sub


Please identify if I made a mistake in my edits. Anyone else is welcom
to help.

Regards

NB: I can attach the file if that could help
 
You can't attach files here. Looks OK if run from WDI data!C3.

The only thing might be
x = WorksheetFunction.CountA(Range("C:C")) - 2
Click to expand...

, if there are "variable definitions" in C1:C2 of WDI data! (I can't really
tell from what you said).

Rgds,
Andy
 
Back
Top