Hi,

The following codes work but does not copy paste special value ( ie
copy value without formula )
Several attempts to modify it but it fails

Extract of codes : -

With Worksheets("BS Grp")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "10A" Then
iNextRow = iNextRow + 1
.Rows(i).Copy Worksheets("Sheet3").Cells(iNextRow,
"A").PasteSpecial xlPasteValues
End If
Next i
End With
Sheets("Sheet3").Columns.AutoFit

Any help would be appreciated and thanks
I'm excel vba beginner

Regards
Len

On Sun, 10 Jan 2010 06:55:17 -0800 (PST), Len
wrote:

Hi,

The following codes work but does not copy paste special value ( ie
copy value without formula )
Several attempts to modify it but it fails

Extract of codes : -

With Worksheets("BS Grp")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "10A" Then
iNextRow = iNextRow + 1
.Rows(i).Copy Worksheets("Sheet3").Cells(iNextRow,
"A").PasteSpecial xlPasteValues
End If
Next i
End With
Sheets("Sheet3").Columns.AutoFit

Any help would be appreciated and thanks
I'm excel vba beginner

Regards
Len



The code seems to work if you just make a new line after the Copy.
Like this:

..Rows(i).Copy
Worksheets("Sheet3").Cells(iNextRow,"A").PasteSpec ial xlPasteValues

Hope this helps / Lars-Åke

I just tested this fine.

Option Explicit
Sub CopyValues()
Dim i As Long
Dim ilastrow As Long
Dim inextrow As Long
With Worksheets("sheet10")
ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To ilastrow
If .Cells(i, "A").Value = "10A" Then
inextrow = inextrow + 1
.Rows(i).Copy
Worksheets("Sheet11").Cells(inextrow, "a") _
.PasteSpecial xlPasteValues
End If
Next i
End With
Sheets("Sheet3").Columns.AutoFit
End Sub
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Len" wrote in message
...
Hi,

The following codes work but does not copy paste special value ( ie
copy value without formula )
Several attempts to modify it but it fails

Extract of codes : -

With Worksheets("BS Grp")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "10A" Then
iNextRow = iNextRow + 1
.Rows(i).Copy Worksheets("Sheet3").Cells(iNextRow,
"A").PasteSpecial xlPasteValues
End If
Next i
End With
Sheets("Sheet3").Columns.AutoFit

Any help would be appreciated and thanks
I'm excel vba beginner

Regards
Len

On Jan 10, 11:13*pm, "Don Guillett" wrote:
I just tested this fine.

Option Explicit
Sub CopyValues()
Dim i As Long
Dim ilastrow As Long
Dim inextrow As Long
With Worksheets("sheet10")
* * ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
* * For i = 1 To ilastrow
* * If .Cells(i, "A").Value = "10A" Then
* * inextrow = inextrow + 1
* * .Rows(i).Copy
* * Worksheets("Sheet11").Cells(inextrow, "a") _
* * .PasteSpecial xlPasteValues
* * End If
* * Next i
* * End With
* * Sheets("Sheet3").Columns.AutoFit
End Sub
* * * If desired, send your file to my address below. I will only look if:
* * * 1. You send a copy of this message on an inserted sheet
* * * 2. You give me the newsgroup and the subject line
* * * 3. You send a clear explanation of what you want
* * * 4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Len" wrote in message

...



Hi,

The following codes work but does not copy paste special value ( ie
copy value without formula )
Several attempts to modify it but it fails

Extract of codes : -

With Worksheets("BS Grp")
* *iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
* *For i = 1 To iLastRow
* *If .Cells(i, "A").Value = "10A" Then
* *iNextRow = iNextRow + 1
* *.Rows(i).Copy Worksheets("Sheet3").Cells(iNextRow,
"A").PasteSpecial xlPasteValues
* *End If
* *Next i
* *End With
* *Sheets("Sheet3").Columns.AutoFit

Any help would be appreciated and thanks
I'm excel vba beginner

Regards
Len- Hide quoted text -

- Show quoted text -

Hi All,

Thanks your prompt reply
I didn't realise it just a simple mistake that cause an error in
running my codes
thanks anyway

Regards
Len