How to transfer specific range to another sheet?

[mtbthepro]

This is below isn't working. I tried to find Account total and then copy everything from that row in between column "D:H" then transfer it to range c27 in sheet "AR SUMMARY". It doesn't seem to do anything. Is there a solution for this?

Sub transfering_Rental()Sheets("Space Rental").Activate
Set Sht = ActiveWorkbook.ActiveSheet
nlast = Cells(Rows.Count, "A").End(xlUp).Row
                    
                    For n = nlast To 1 Step -1
                   If Sht.Cells(n, 2).Value = "ACCOUNT TOTAL" Then
            
                            Sht.Rows(n).Range("D:H" & n).Copy
                            Sheets("AR SUMMARY").Range("C27" & n).PasteSpecial xlPasteValues
                        End If
          Next n
         
End Sub

 

[Llupo01]

this is your code my friend

Option Explicit
Option Compare Text


Sub transfering_Rental()
Dim nlast As Long
Dim sht As Worksheet
Dim n As Integer

Sheets("Space Rental").Activate
Set sht = ActiveWorkbook.ActiveSheet
nlast = Cells(Rows.Count, "A").End(xlUp).Row
                    
For n = nlast To 1 Step -1
  If sht.Cells(n, 2).Value = "ACCOUNT TOTAL" Then           
   Range(Cells(n, "D"), Cells(n, "H")).Copy
   Sheets("AR SUMMARY").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  End If
Next n
End Sub

 

[mtbthepro]

Thank you for the reply but unfortunately not doing anything. Nothing happens

 

[Llupo01]

where is the value "Account total"? in which column? If I suppose, that this value si in B columns, which was your macro looking for, then it should work ok...just tried it again and all good

 

[mtbthepro]

where is the value "Account total"? in which column? If I suppose, that this value si in B columns, which was your macro looking for, then it should work ok...just tried it again and all good

Yes it is in Column B but where is it pasteing in "AR SUMMARY"? Tried it again didn't work lol

 

[Llupo01]

try this, I dont know, how consistent are your data in column A...if there can be possibility, that column A will have less values than column B, it might be cause of not working this code..try this

Option Explicit
Option Compare Text


Sub transfering_Rental()
Dim nlast As Long
Dim sht As Worksheet
Dim n As Integer

Sheets("Space Rental").Activate
Set sht = ActiveWorkbook.ActiveSheet
nlast = Cells(Rows.Count, "B").End(xlUp).Row
                    
For n = nlast To 1 Step -1
  If sht.Cells(n, 2).Value = "ACCOUNT TOTAL" Then
   Range(Cells(n, "D"), Cells(n, "H")).Copy
   Sheets("AR SUMMARY").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  End If
Next n
End Sub

 

[mtbthepro]

Okay it works now but its pasting in the wrong area. How do I pick a range? Range for this is C27 in AR SUMMARY

 

[mtbthepro]

Got it, works great. Thank you.

 

[Llupo01]

you are welcome