How do I reference range of columns for my copy/paste VBA code?

[Jeevz_87]

Hi All,


I've been busy creating a code that, depending on whether the values in a column are greater/less than 0, the code will copy those values to the bottom of the table and paste them into a corresponding table.

The problem Im having is that Ive had to repeat this code for each column and am unsure on how to reference a range of columns for the copy and paste portion of my code.

I want my code to follow this logic;

'If values in table columns Z:BI are greater than/less than zero, then select those values only , copy only those values and paste them in corresponding range BL:CU. Else if values equal zero, do nothing.'

My existing code Im using currently loops from row 12 down to the bottom of the table in a column before pasting, however this is abit slow and only focuses on one column at a time.

Sub CopyValuesP1FY23()
    
    Dim WS As Worksheet
    Dim iLastRow As Long
    Dim i As Long
    
   
    Set ws = ActiveSheet
        
    
    Let iLastRow = ws.Cells(WS.Rows.Count, "Z").End(xlUp).Row
        
    
    For i = 12 To iLastRow
       
        
        If ws.Cells(i, 26).Value > 0 Then
            
           
            ws.Cells(i, 64).Value = ws.Cells(i, 26).Value
            
        
        ElseIf ws.Cells(i, 26).Value < 0 Then
            
            
            ws.Cells(i, 64).Value = ws.Cells(i, 26).Value
                
        End If
            
    Next i
        
End Sub

Is there a way to do what I'm proposing using the above example but incorporating the above ranges (i.e Z:BI Copy, BL:CU Paste) and maybe speeding up the process by using a less process intensive code?

Thanks,



Jeevz

 

[HaHoBe]

Hi Jeevz,

how about

Sub MrE1222730_161450F()
    
  Dim WS As Worksheet
  Dim rngCell As Range
  
  Application.ScreenUpdating = False
  Set WS = ActiveSheet
  
  For Each rngCell In WS.Range("Z12", WS.Range("BI" & WS.Cells(WS.Rows.Count, "Z").End(xlUp).Row))
    If rngCell.Value <> 0 Then rngCell.Offset(, 28).Value = rngCell.Value
  Next rngCell
        
  Set WS = Nothing
  Application.ScreenUpdating = True

End Sub

Ciao,
Holger