Loop: Copy and paste range that changes with every iteration
 

Perfect! Without the quotes, it works flawlessly!! Thank you very much for
your help!!

-Allen

"Jim Thomlinson" wrote:

Remove the quotes... I should not have left them in my original response...

Sub Rearrange()

Dim SearchVariable As String
Dim rngCopy As Range
Dim rngPaste As Range
Dim a As Integer
Dim x As Integer

a = 2
c = 1

Do While Worksheets("Correct Order").Cells(a, 1).Value < ""

SearchVariable = Worksheets("Correct Order").Cells(a, 1).Value

b = 1

Do While x < 1

If Worksheets("Raw Data").Cells(b, 1).Value = SearchVariable Then

With Worksheets("Raw Data")
Set rngCopy = .Range(.Cells(b, 1), .Cells(b, 10))
End With

With Worksheets("Sorted Data")
Set rngPaste = .Range(.Cells(c, 1), .Cells(c, 10))
End With

rngCopy.Copy rngPaste

c = c + 1
x = 1

End If

b = b + 1

Loop

a = a + 1
x = 0

Loop

End Sub

--
HTH...

Jim Thomlinson


"Allen Geddes" wrote:

Thank you both for your replies! Jim, I can follow your logic perfectly!
However, when I try and implement your code, I get a "Type Mismatch" error on
the line which reads: "set rngCopy = .range(.cells("B", 1),.cells("B", 10))"


I'll paste the whole code... I apologize if it's not written in the correct
sequence (I'm not really a programmer...), but it makes perfect sense to me!

Sub Rearrange()

Dim SearchVariable As String
Dim rngCopy As Range
Dim rngPaste As Range
Dim a As Integer
Dim x As Integer

a = 2
c = 1

Do While Worksheets("Correct Order").Cells(a, 1).Value < ""

SearchVariable = Worksheets("Correct Order").Cells(a, 1).Value

b = 1

Do While x < 1

If Worksheets("Raw Data").Cells(b, 1).Value = SearchVariable Then

With Worksheets("Raw Data")
Set rngCopy = .Range(.Cells("b", 1), .Cells("b", 10))
End With

With Worksheets("Sorted Data")
Set rngPaste = .Range(.Cells("c", 1), .Cells("c", 10))
End With

rngCopy.Copy rngPaste

c = c + 1
x = 1

End If

b = b + 1

Loop

a = a + 1
x = 0

Loop

End Sub




"Jim Thomlinson" wrote:

Without seeing all of the code this will have to be a bit general...

Dim rngCopy as range
Dim rngPaste as range

with Worksheets("Raw Data")
set rngCopy = .range(.cells("B", 1),.cells("B", 10))
end with

with Worksheets("Sorted Data")
set rngPaste = .range(.cells("C", 1),.cells("C", 10))
end with

rngCopy.copy rngPaste


--
HTH...

Jim Thomlinson


"Allen Geddes" wrote:

I'm trying to copy a row and paste it in another worksheet. My Problem is
declaring the range using variables that are a different value with every
iteration of the loop. I know the syntax below is horribly wrong, but I
can't find the proper syntax for what I'm trying to do!


Worksheets("Raw Data").Range(b & "1", b & "10").Copy
Destination:=Worksheets("Sorted Data").Range(c & "1", c & "10")


I want it to copy from the range Cells((b,1):(b,10)) and past them in
another worksheet to the range Cells((c,1):(c,10))

I'm very new to this whole VBA in Excel thing, so please forgive me if this
has already been asked. A search didn't yield what I was looking for...
Thanks

-Allen