Shift Data to a Specified Row

[rwmill9716]

I collect data in columns A and B. I need a function statement or macro that reads cell C1 and then shifts my data to that row in columns C and D.

Thanks for your help
Ric

Excel 2012
	A	B	C	D
1		Starting Row:	9
2	Date/Time	Factor 2	Date/Time	Reset
3	8/12/2016 12:55	55.55
4	8/13/2016 0:03	56.11
5	8/14/2016 0:03	56.21
6	8/15/2016 0:00	56.10
7	8/16/2016 6:00	56.15
8	8/17/2016 0:21	55.92
9	8/23/2016 0:05	56.27	8/12/2016 12:55	55.55
10	8/24/2016 0:05	56.17	8/13/2016 0:03	56.11
11	8/24/2016 21:00	56.32	8/14/2016 0:03	56.21
12	8/25/2016 22:30	56.30	8/15/2016 0:00	56.10
13	8/26/2016 22:47	56.37	8/16/2016 6:00	56.15
14	8/27/2016 22:36	56.30	8/17/2016 0:21	55.92
Sheet2

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG07Dec46
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
Rng.Resize(, 2).Cut Cells([c1], "C")
'[COLOR="Green"][B]Change "Cut" to "Copy" if thats what you want[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[BarryL]

maybe:

Sub shift_data()
Dim lr As Long, rowno As Long
Let lr = Range("A" & Rows.Count).End(xlUp).Row
Let rowno = Range("C1").Value
Range("A3:B" & lr).Copy
Range("C" & rowno).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[rwmill9716]

Thanks, MickG and BarryL, both of these work well.

Ric

 

[MickG]

You're welcome