Copy & Paste Range Into New Sheet

[hamistasty]

For each value in column A after row 9, I want a macro to copy the cells of that row in column A, C, & E. I then want it to paste those 3 values of each row into a new sheet in Column A, B and C in the same order. The with statement ends when A & i is blank.

Would appreciate a start in the right direction on this. Thanks!

 

[BrandonWLH]

dim x as integer
x = 0
dim rng1 as range
dim rng2 as range
set rng1 = Worksheets("Sheet1").Range("A9")
set rng2 = Worksheets("Sheet2").Range("A1")

do while isnotempty(rng1.offset(x,0))
   rng2.offset(x,0) = rng1.offset(x,0)
   rng2.offset(x,1) = rng1.offset(x,2)
   rng2.offset(x,2) = rng1.offset(x,4)
   x=x+1
loop

Might be a couple errors in there since it was free hand typed, but thats the jist. It loops through until it finds an empty spot in A on sheet 1. Offset(row,column) changes the location of your data transfer. Since both start in column A, Offset(0,1) would be column b. Offset(0,4) would be column e, etc.

 

[hamistasty]

Thanks, works great. The only thing you got wrong was this line, which I corrected:

Do While Not IsEmpty(rng1.Offset(x, 0))

 

[hamistasty]

What I want to do now is use the following code but change it so that now, when it copies one row to 'Index' into the cells (y, 2), (y, 6), (y, 8) from 'Schedule', as it currently does- instead of replacing the cells down from that first row, it copies and inserts that entire previous row and fills out the newly inserted row.

Sub Index()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim x As Integer
x = 0
y = 13
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("Schedule").Range("A9")
Set rng2 = Worksheets("Index").Range("A1")
Do While Not IsEmpty(rng1.Offset(x, 0))
   rng2.Offset(y, 2) = rng1.Offset(x, 0)
   rng2.Offset(y, 6) = rng1.Offset(x, 1)
   rng2.Offset(y, 8) = rng1.Offset(x, 14)
   x = x + 1
Loop
        
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
 End Sub

Any help?

 

[hamistasty]

bump. Any ideas?