I have what I would say is a easy problem to solve but I can't!. I want to
run a macro that will automatically 'copy paste special values' all cells in
a row between B* and Z* if the value in A* equates to a value in Cell A2.
This will be run over several worksheets



Any ideas?



Thanks

Your problem statement leaves a lot of ambiguity, but perhaps you can
adapt this:
'


Public Sub CopyStuff()
Dim rCell As Range
Dim rDest As Range
Dim wsSheet As Worksheet
Dim dTest As Double

dTest = Worksheets("Sheet1").Range("A2").Value
Set rDest = Worksheets("Sheet1").Range("A5").Resize(, 25)
For Each wsSheet In Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
With wsSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If .Value = dTest Then
rDest.Value = _
.Offset(0, 1).Resize(, 25).Value
Set rDest = rDest.Offset(1, 0)
End If
End With
Next rCell
End With
Next wsSheet
End Sub

This copies rows from B* to Z* in sheets 2,3 and 5 for which the value
in A* equals the value in Sheet1, cell A2. It copies the values to
Sheet1, beginning at cell A5.



In article ,
"John" wrote:

I have what I would say is a easy problem to solve but I can't!. I want to
run a macro that will automatically 'copy paste special values' all cells in
a row between B* and Z* if the value in A* equates to a value in Cell A2.
This will be run over several worksheets



Any ideas?



Thanks

Thanks for your reply. I probably should have included that the 'copy paste
values' is required in the same cells (what I'm doing is converting formulas
in these cells to the values that they have calculated

Thanks




"JE McGimpsey" wrote in message
...
Your problem statement leaves a lot of ambiguity, but perhaps you can
adapt this:
'


Public Sub CopyStuff()
Dim rCell As Range
Dim rDest As Range
Dim wsSheet As Worksheet
Dim dTest As Double

dTest = Worksheets("Sheet1").Range("A2").Value
Set rDest = Worksheets("Sheet1").Range("A5").Resize(, 25)
For Each wsSheet In Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
With wsSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If .Value = dTest Then
rDest.Value = _
.Offset(0, 1).Resize(, 25).Value
Set rDest = rDest.Offset(1, 0)
End If
End With
Next rCell
End With
Next wsSheet
End Sub

This copies rows from B* to Z* in sheets 2,3 and 5 for which the value
in A* equals the value in Sheet1, cell A2. It copies the values to
Sheet1, beginning at cell A5.



In article ,
"John" wrote:

I have what I would say is a easy problem to solve but I can't!. I want
to
run a macro that will automatically 'copy paste special values' all
cells in
a row between B* and Z* if the value in A* equates to a value in Cell
A2.
This will be run over several worksheets



Any ideas?



Thanks

That's a bit easier:

Public Sub EliminateFormulae()
Dim wsSheet As Worksheet
Dim dTest As Double
dTest = Worksheets("Sheet1").Range("A2").Value
For Each wsSheet In Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
With wsSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Value = dTest Then
With rCell.Offset(0, 1).Resize(, 25)
.Value = .Value
End With
End If
Next rCell
End With
Next wsSheet
End Sub


In article ,
"John" wrote:

Thanks for your reply. I probably should have included that the 'copy paste
values' is required in the same cells (what I'm doing is converting formulas
in these cells to the values that they have calculated

Works a treat JE thanks for that

"JE McGimpsey" wrote in message
...
That's a bit easier:

Public Sub EliminateFormulae()
Dim wsSheet As Worksheet
Dim dTest As Double
dTest = Worksheets("Sheet1").Range("A2").Value
For Each wsSheet In Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
With wsSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Value = dTest Then
With rCell.Offset(0, 1).Resize(, 25)
.Value = .Value
End With
End If
Next rCell
End With
Next wsSheet
End Sub


In article ,
"John" wrote:

Thanks for your reply. I probably should have included that the 'copy
paste
values' is required in the same cells (what I'm doing is converting
formulas
in these cells to the values that they have calculated

One slight problem, it seems to continue across all columns if there is a
formula within i.e. from B* out. How do I get it to stop 'copy-paste-values'
at say column D?

Thanks


"John" wrote in message
...
Works a treat JE thanks for that

"JE McGimpsey" wrote in message
...
That's a bit easier:

Public Sub EliminateFormulae()
Dim wsSheet As Worksheet
Dim dTest As Double
dTest = Worksheets("Sheet1").Range("A2").Value
For Each wsSheet In Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
With wsSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Value = dTest Then
With rCell.Offset(0, 1).Resize(, 25)
.Value = .Value
End With
End If
Next rCell
End With
Next wsSheet
End Sub


In article ,
"John" wrote:

Thanks for your reply. I probably should have included that the 'copy
paste
values' is required in the same cells (what I'm doing is converting
formulas
in these cells to the values that they have calculated

Worked it out Resize(, 25) = Column numbers

Thanks


"John" wrote in message
...
One slight problem, it seems to continue across all columns if there is a
formula within i.e. from B* out. How do I get it to stop
'copy-paste-values'
at say column D?

Thanks


"John" wrote in message
...
Works a treat JE thanks for that

"JE McGimpsey" wrote in message
...
That's a bit easier:

Public Sub EliminateFormulae()
Dim wsSheet As Worksheet
Dim dTest As Double
dTest = Worksheets("Sheet1").Range("A2").Value
For Each wsSheet In Sheets(Array("Sheet2", "Sheet3",
"Sheet5"))
With wsSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Value = dTest Then
With rCell.Offset(0, 1).Resize(, 25)
.Value = .Value
End With
End If
Next rCell
End With
Next wsSheet
End Sub


In article ,
"John" wrote:

Thanks for your reply. I probably should have included that the
'copy
paste
values' is required in the same cells (what I'm doing is converting
formulas
in these cells to the values that they have calculated

In using the the formula to value I get stuck on (For Each rCell ) Message
Compile Error Can find project or Library


"John" wrote in message
...
Worked it out Resize(, 25) = Column numbers

Thanks


"John" wrote in message
...
One slight problem, it seems to continue across all columns if there is
a
formula within i.e. from B* out. How do I get it to stop
'copy-paste-values'
at say column D?

Thanks


"John" wrote in message
...
Works a treat JE thanks for that

"JE McGimpsey" wrote in message
...
That's a bit easier:

Public Sub EliminateFormulae()
Dim wsSheet As Worksheet
Dim dTest As Double
dTest = Worksheets("Sheet1").Range("A2").Value
For Each wsSheet In Sheets(Array("Sheet2", "Sheet3",
"Sheet5"))
With wsSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Value = dTest Then
With rCell.Offset(0, 1).Resize(, 25)
.Value = .Value
End With
End If
Next rCell
End With
Next wsSheet
End Sub


In article ,
"John" wrote:

Thanks for your reply. I probably should have included that the
'copy
paste
values' is required in the same cells (what I'm doing is
converting
formulas
in these cells to the values that they have calculated