ExcelBanter - macro loop

I think you'll have to give more info to get really specific help....

But something like this may get you started.

Option Explicit
Sub testme()

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim RngToCopy As Range
Dim DestCell As Range

Set wks1 = Worksheets("sheet1")
Set wks2 = Worksheets("sheet2")

With wks1
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Set RngToCopy = myCell.Offset(0, 4).Resize(1, 10)
With wks2
Set DestCell = .range("z99")
End With

RngToCopy.Copy _
Destination:=DestCell

wks2.Range("a1").Copy _
Destination:=myCell.Offset(0, 1)

Next myCell

End Sub

I loop through the cells in column A of sheet1.
I copy from 4 columns over (for 10 columns) to sheet2 starting in Z99.
Then I copy A1 from Sheet2 and paste it into sheet1 in column B of that same
row.

DM wrote:

I am trying to write a macro that, after starting at a given reference point,
loops to do the following action for several hundred lines in Excel:

-Moves left a constant number of columns
-Highlights and copies 10 horizontally adjacent cells
-Jumps to another tab
-Pastes the copied values into a given range of cells on the new tab
-Moves to a given cell on the new tab then copies the value from that given
cell
-Moves back to the initial tab
-Pastes the copied value into a given cell on the initial tab

The loop should then tell the sheet to move down one row and repeat the
procedure (moving left a constant number of columns, highlighting and
copying, jumping to another tab, etc).

Any thoughts on how to do this in visual basic? Any help would be very much
appreciated! Thank you in advance.

--

Dave Peterson