copy selected rows to new sheet
 

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

copy selected rows to new sheet
 

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

copy selected rows to new sheet
 

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