Loop: Copy and paste range that changes with every iteration

[Guest]

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

 

[tiah]

For i = 1 To Range("A1").CurrentRegion.Rows.Count
Sheets(1).Columns(i).Copy Destination:=Sheets(2).Columns(i + 1)
Next



tiah.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

-Allen