copy selected rows to new sheet

[Bryan]

My source_sheet contains data that is to be filtered with the accepted row
being copied to target_sheet. Here is a fragment that shows the goal in
pseudo code formatting. Where I have a hard number, code somewhere else has
found that number and provided it.

source_sheet and target_sheet are both Worksheet variables

first_row = 6 ' begin copying with this row
last_row = 134 ' stop copying at this row
target_row = 2 'put first row in row 2 of the target sheet
first_column = 1 ' I have this number if I need it
last_column = 18 ' I have this number if I need it

for source_row = first_row to last_row
If some_condition then
source_sheet.cells( all of row source_row).copy
target_sheet.cels( all of target_row ).paste
target_row = target_row + 1
end if
next source_row

I am not very good at digging through complex Excel syntax so I ask that
responses be in a simple format so I can understand. I need two statements
that do the following:
1) copy all of row number source_row from the source sheet,
2) paste that copied row into the target_sheet in row specified by
target_row

I want to be certain that the question is clear, but this is already too
long. Let me know if clarifications are in order.

Thanks,
Bryan

[mudraker]

Bryan

Try this single line of code (I have wrapped it to make it easy to copy and paste straight into your macro with out having to worry about the single line of code getting wrapped in this message)


This copys 1 row of data from the source sheet. The source row number = source_row of your for loop.

source_sheet.rows(source_row).copy _
Destination:=rngDest target_sheet.rows(Target_row )

[Bryan]

After much serching I pulled some code from someone's posting that uses this
concept. After modifying to meed my needs, I get stuck.

When source_sheet and target_sheet are both set to "Sheet1", this code
works.

When target_sheet is set to "Sheet2", there is an error on this line:

Set target_range = target_sheet.Range(Cells(target_row, 1),
Cells(target_row, 7))

The popup window says:

Run-time error '1004':

Method 'Range' of object '_Worksheet' failed

I have two sheets in the workbook, one named Sheet1 and the other named
Sheet2. Sheet1 has data in the cells of interest while Sheet2 is blank.

What do I need to change to get this to copy to Sheet 2.

Here is the code.

Sub copy_test()

Dim source_sheet As Worksheet

Dim target_sheet As Worksheet

Dim target_row As Integer

Dim source_row As Integer

Dim source_range As Range

Dim target_range As Range

Set source_sheet = Worksheets("Sheet1")

Set target_sheet = Worksheets("Sheet2")

For source_row = 1 To 6

Set source_range = source_sheet.Range(Cells(source_row, 1),
Cells(source_row, 7))

target_row = source_row + 7

Set target_range = target_sheet.Range(Cells(target_row, 1),
Cells(target_row, 7))

source_range.Copy target_range

Next source_row

End Sub

Thanks for your time,

"Bryan" <no-one@no-spam.com> wrote in message
news:4jPUd.96573$pc5.26831@tornado.tampabay.rr.com...
> My source_sheet contains data that is to be filtered with the accepted row
> being copied to target_sheet. Here is a fragment that shows the goal in
> pseudo code formatting. Where I have a hard number, code somewhere else
> has found that number and provided it.
>
<snip>

[Leith Ross]

Hello Bryan,

Two lines of code can copy data from the source worksheet to the target worksheet.

Example - Sheet1 is the source, Sheet 2 is the target
Both Ranges must have same number of Rows and Columns

Worksheets("Sheet1").Range("A1:D4").Copy
Worksheets("Sheet2").Range("D2:G5").PasteSpecial xlAll

In your code, assuming the Columns are "A" to "R" (1 to 18) on both sheets and Rows on Source sheet are 6 to 134 and Target Rows are 2 to 130 then the code would be:

Worksheets("Sheet1").Range("A6:R134").Copy
Worksheets("Sheet2").Range("A2:R130").PasteSpecial xlAll
_________________________________________________________________

This should make it clear for you. If not repost any questions you have and I'll be glad to answer them.

Leith Ross

[A.New]

I was just working on a similar problem. I too was having trouble pasting a
range from 1 sheet to another. I ended up working with cells and cells
offset. I ended up just setting the target cell = to the source cell- no
pasting involved. See if works the same for your target range = source range.
Alfred

"Bryan" wrote:

> After much serching I pulled some code from someone's posting that uses this
> concept. After modifying to meed my needs, I get stuck.
>
> When source_sheet and target_sheet are both set to "Sheet1", this code
> works.
>
> When target_sheet is set to "Sheet2", there is an error on this line:
>
> Set target_range = target_sheet.Range(Cells(target_row, 1),
> Cells(target_row, 7))
>
> The popup window says:
>
> Run-time error '1004':
>
> Method 'Range' of object '_Worksheet' failed
>
> I have two sheets in the workbook, one named Sheet1 and the other named
> Sheet2. Sheet1 has data in the cells of interest while Sheet2 is blank.
>
> What do I need to change to get this to copy to Sheet 2.
>
> Here is the code.
>
> Sub copy_test()
>
> Dim source_sheet As Worksheet
>
> Dim target_sheet As Worksheet
>
> Dim target_row As Integer
>
> Dim source_row As Integer
>
> Dim source_range As Range
>
> Dim target_range As Range
>
> Set source_sheet = Worksheets("Sheet1")
>
> Set target_sheet = Worksheets("Sheet2")
>
> For source_row = 1 To 6
>
> Set source_range = source_sheet.Range(Cells(source_row, 1),
> Cells(source_row, 7))
>
> target_row = source_row + 7
>
> Set target_range = target_sheet.Range(Cells(target_row, 1),
> Cells(target_row, 7))
>
> source_range.Copy target_range
>
> Next source_row
>
> End Sub
>
> Thanks for your time,
>
> "Bryan" <no-one@no-spam.com> wrote in message
> news:4jPUd.96573$pc5.26831@tornado.tampabay.rr.com...
> > My source_sheet contains data that is to be filtered with the accepted row
> > being copied to target_sheet. Here is a fragment that shows the goal in
> > pseudo code formatting. Where I have a hard number, code somewhere else
> > has found that number and provided it.
> >
> <snip>
>
>
>