stack range of data to next empty row in other sheet Solved/Closed

Question

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?

TrowaD · Answer

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

TrowaD · Answer

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

Stack range of data to next empty row in other sheet

2 responses

Similar discussions

Subscribe To Our Newsletter!