Stuck!

  • Thread starter Thread starter John
  • Start date Start date
J

John

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.
 
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
 
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
 
Works a treat JE thanks for that

JE McGimpsey said:
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
Click to expand...
 
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
 
In using the the formula to value I get stuck on (For Each rCell ) Message
Compile Error Can find project or Library
 
Back
Top