copy and paste code

[dennisli]

Good afternoon, everyone
I have a customer ID list in numerical format such as 678456 from Sheet1!B8:B150 and I want to copy and paste to Sheet2 from range A26 by 10 steps. For example, Sheet1!B8 to Sheet2!A26, Sheet1!B9 to Sheet2!A36, Sheet1!b10 to Sheet2!A46, etc. But for each customer ID in Sheet2, I want to put sign of Customer ID before the 678456 to become: Customer ID 678456.
Thanks lot.
Dennis

 

[MrKowz]

Dennis,

Try the following macro:

Public Sub CopyCustomerIDs()
Dim i   As Long, _
    ws1 As Worksheet, _
    ws2 As Worksheet, _
    LR  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
LR = ws1.Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LR
    ws2.Range("A" & 26 + ((8 - i) * 10)).Value = "Customer ID " & ws1.Range("B" & i).Value
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[p45cal]

untested:

Sub blah()
DestRow = 26    'starting value
For Each cll In Sheets("Sheet1").Range("B8:B150")
    Sheets("Sheet2").Range("A" & DestRow).Value = "Customer ID " & cll.Value
    DestRow = DestRow + 10
Next cll
End Sub

 

[dennisli]

Wonderful. Have a good day.