[SOLVED] Loop: Copy and paste range that changes with every iteration

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

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



tiah.

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

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

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

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