Stack range of data to next empty row in other sheet

Solved/Closed
milli - Aug 12, 2015 at 05:34 AM
 Milli - Aug 18, 2015 at 01:59 AM
Hello,

I have a set of data in Sheet1 Range("D24:D50") and Range("AC24:AC50") would like to be copied and paste special value to Sheet2 Column A and B by using a quick button and this step need to be repeated everyday to the next new row at Column A and B since there will be a new sets of value be filled under Sheet1 Range("D24:D50") and Range("AC24:AC50").

I was using below codes but it always replace my old data on Sheet2 instead of paste to the new row and it does not copy data from D24:D50 but only D24..

Sheets("Sheet1").Select

Temp1 = Range("D24:D50").Value
Temp2 = Range("AC24:AC50").Value

Sheets("Sheet2").Select

i = 2
Do While Len(Trim(Range("n" & i).Value)) <> 0
i = i + 1
Loop

Range("A" & i).Value = Temp1
Range("B" & i).Value = Temp2

End Sub


Can you please help me?

2 replies

TrowaD
Posts
2888
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 16, 2022
515
Aug 13, 2015 at 11:26 AM
Hi Milli,

What is the deal with all the dots?

Try the following code:
Sub RunMe()
Dim rng1, rng2 As Range

Sheets("Sheet1").Select
Set rng1 = Range("D24:D50")
Set rng2 = Range("AC24:AC50")

Sheets("Sheet2").Select
rng1.Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rng2.Copy Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub


Best regards
0
Many thanks TrowaD !! Please ignore the dots.. I forgot to mention that Range("D24:D50") is with formula but I only want to copy the text but no the formula.. Could you please guide me how should I edit the codes? Thanks in advance!
0
TrowaD
Posts
2888
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 16, 2022
515
Aug 17, 2015 at 10:37 AM
Hi Milli,

Sure thing, for that use the following code:
Sub RunMe()
Dim rng1, rng2 As Range

Sheets("Sheet1").Select
Set rng1 = Range("D24:D50")
Set rng2 = Range("AC24:AC50")

Sheets("Sheet2").Select
rng1.Copy
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
rng2.Copy Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub


Best regards,
Trowa
0
Many thanks Trowa for your time! Wish you a very great day ahead!
0