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

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" wrote in message
...
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

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" wrote in message
...
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