Relative Cut/Paste & Clear Contents

[tape deck]

AkutInv.xls
	A	B	C	D	E	F	G	H	I	J
186	MISCELLANEOUSFISH
187	BLACKBASS-60#	17	1,020	13	780		0	30	1,800	780
188	SKATELN-44#	0	0	45	1,980		0	45	1,980	1,980
189	RAYRN-44#	0	0		0		0	0	0	4,972
190	MACKEREL50#	0	0		0		0	0	0	154,432
191	YELLOWEYEROCKFISH50#	48	2,400		0		0	48	2,400	2,400
192	BLACKCODTRAWLBYCATCH-TOTES	1	722		0		0	1	722	722
193	IDIOTFISH50#	0	0		0		0	0	0	4,200
194	AKFLOUNDER-50#	0	0		0		0	0	0	42,550
195	PACIFICOCEANPERCH-50#	0	0		0		0	0	0	151,400
196	TOTALMISCELLANEOUSFISHPRODUCT	66	4,142	58	2,760	0	0	124	6,902	363,436
341 (2)

Hi. This seems like it should be easy. Maybe I've just been at work to long today.

I'm trying to write a macro that'll copy the contents of column J and paste them in column K. Problem is that the number of cells above and below will change from time to time as people insert lines. Regardless, what is in the J column between the MISCELLANEOUS and TOTAL MISCELLANEOUS FISH PRODUCT rows always needs to be pasted in the next cells over in column K.

I have many spreadsheets set up like this, but declaring set ranges(i.e. copy J187:J196, paste K187), but every time a row is inserted in the spreadsheet the macro needs to be revised.

FYI:
Column B is cases on hand (balance forward)forward. Column C is weight on hand(balance forward).
Column D is today's # of cases produced. Column E is today's # of pounds produced.
Column F is today's cases shipped. Column G is today's pounds shipped.
Column H is cases on hand. Column I is pounds on hand.
Column J is Fiscal YTD pounds produced. Column K is Fiscal YTD(balance forward).

This macro is run once every morning.

Thank you very much for any and all help.

 

[Nimrod]

' SAMPLE 1
Sub CopyAndPaste()
BotRow = Range("J2").End(xlDown).Row
Range("J2:J" & BotRow).Copy
ActiveSheet.Paste Destination:=Range("K2")
Application.CutCopyMode = False
End Sub

' SAMPLE 2
Sub CopyValueDemo()
For Each c In Range("J:J").SpecialCells(xlCellTypeFormulas, 3)
c.Offset(0, 1).Value = c.Value
Next c
End Sub

' SAMPLE 3
Sub CopyAndPaste()
BotRow = Range("J2").End(xlDown).Row - 1
Range("J2:J" & BotRow).Copy
ActiveSheet.Paste Destination:=Range("K2")
Application.CutCopyMode = False
End Sub