Hi All,
I was wondering if you could help with my code. I am trying to reference Column E1 in Sheet Comm ( source) to Column I2 in Sheet CSV ( destination) 150 times. Once it is done populating the first round, I want now Column F1 in Sheet Comm ( source) to Column I151 in Sheet CSV ( destination) 150 times. The code runs till the first empty cell in row 1 Sheet Comm ( source). there are 67 values that i would like to reference in Sheet CSV in column I.
what works in my code:
- Sheets("CSV").Range("I" & r).Value = "=right(Comm!e1,5)" ' paste community code to the first 150 cells in column I -
what doesn't work:
- how do I make it change to F1 after it completes the first round of looping?
Thank you for all your help!
Code
Sub test()
Dim r As Integer, c As Integer
Do While Sheets("Comm").Cells(1, Column.Count).End(xlToLeft).Column 'Do while the cell in the first row is not blank
For r = 2 To 150 'rows that referencing in comm sheet
Sheets("CSV").Range("I" & r).Value = "=right(Comm!e1,5)" ' paste community code to the first 150 cells in column I
Next
Loop
End Sub
Display More