Hi,

I am trying to go through all my worksheets in a workbook- copy a
specific row - and then paste the values to the first empty row after
the row copied.

The going through the worksheets part functions.
The copy functions.
The part where I paste values into the empty row is giving me problems
though.

Let's say - for example - row 3 has data that I want copied. And I
wanna put that data in row 15. The original row and destination row
will always be different (depending on criteria I have in other parts
of my logic).

This is what I have:

Cells.Select
Set rngWs = ActiveCell.CurrentRegion
With ws
Rows(rowToCopy).Copy
Application.CutCopyMode = False
Rows((rngWs.Rows.Count + 1)).Insert
Rows((rngWs.Rows.Count)).PasteSpecial Paste =
xlPasteSpecial
End With

What am I doing incorrectly?

Thanks so much!
-Robin


---
Message posted from http://www.ExcelForum.com/

Do you mean
sheets("sheet1")is your summary?
sheets 2-10 are the sheets you want data from? If so, where on the sheet?
Last row?
copied to the first available row in sheet 1?

then this works

Sub copyem()
For Each ws In Worksheets
If ws.Name < "Sheet1" Then
x = ws.Range("a65536").End(xlUp).Row
'MsgBox x
y = Sheets("Sheet1").Range("a65536").End(xlUp).Row + 1
'MsgBox y
ws.Range("a" & x).Copy _
Sheets("Sheet1").Range("a" & y)
End If
Next ws
End Sub


--
Don Guillett
SalesAid Software

"strataguru" wrote in message
...
Hi,

I am trying to go through all my worksheets in a workbook- copy a
specific row - and then paste the values to the first empty row after
the row copied.

The going through the worksheets part functions.
The copy functions.
The part where I paste values into the empty row is giving me problems
though.

Let's say - for example - row 3 has data that I want copied. And I
wanna put that data in row 15. The original row and destination row
will always be different (depending on criteria I have in other parts
of my logic).

This is what I have:

Cells.Select
Set rngWs = ActiveCell.CurrentRegion
With ws
Rows(rowToCopy).Copy
Application.CutCopyMode = False
Rows((rngWs.Rows.Count + 1)).Insert
Rows((rngWs.Rows.Count)).PasteSpecial Paste =
xlPasteSpecial
End With

What am I doing incorrectly?

Thanks so much!
-Robin


---
Message posted from http://www.ExcelForum.com/

thanks for the response.

i am copying a row for the same worksheet that i want to paste onto.

let's say i have rows 1 through 15 filled with data (test case data in
this case) - and my program determines row 5 needs to be copied
(basically - i wanna replicate the data on row 5) and put it in row 16
- because it's the first available row without any information in it.

so - i wanna copy row 5 and paste the values into row 16.

hope that helps. i tried your code - but i need to reference rows
implicitly.

thanks!


---
Message posted from http://www.ExcelForum.com/

Then, it should be a bit easier. This asks you for the row to copy to the
next available row after the last row
Sub copyrow()
x = Cells(Rows.Count, "a").End(xlUp).Row + 1
Rows(InputBox("Enter row to copy")).Copy Rows(x)
End Sub

--
Don Guillett
SalesAid Software

"strataguru" wrote in message
...
thanks for the response.

i am copying a row for the same worksheet that i want to paste onto.

let's say i have rows 1 through 15 filled with data (test case data in
this case) - and my program determines row 5 needs to be copied
(basically - i wanna replicate the data on row 5) and put it in row 16
- because it's the first available row without any information in it.

so - i wanna copy row 5 and paste the values into row 16.

hope that helps. i tried your code - but i need to reference rows
implicitly.

thanks!


---
Message posted from http://www.ExcelForum.com/

Is this what you are looking for: Rows(rowToCopy).Copy
Destination:=Rows(Range("a65536").End(xlUp).Row + 1)

What you at least do wrong is enter a .copy statement an then say
Application.CutCopyMode = False
With the last statement, you eliminate the previous .copy
statement(s).


strataguru wrote in message ...
thanks for the response.

i am copying a row for the same worksheet that i want to paste onto.

let's say i have rows 1 through 15 filled with data (test case data in
this case) - and my program determines row 5 needs to be copied
(basically - i wanna replicate the data on row 5) and put it in row 16
- because it's the first available row without any information in it.

so - i wanna copy row 5 and paste the values into row 16.

hope that helps. i tried your code - but i need to reference rows
implicitly.

thanks!


---
Message posted from http://www.ExcelForum.com/