Stack range of data to next empty row in other sheet [Solved/Closed]

Report
-
 Milli -
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

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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
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!
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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
Many thanks Trowa for your time! Wish you a very great day ahead!