Defining a new range in macro based on cell match in two worksheets

[mantooth29]

I am looking to write a macro that will copy a row of cells in Sheet 2 (Database) if the value in column B of 'Database' matches A1 in 'Sheet1'.

I have compiled the following incomplete macro:


Sub Stock()

Set i = Sheets("Sheet1").Range("A1")

Set e = Sheets("Database").Range(B)

Set f = Sheets("Database").EntireRow


If i.Range("E1") = e.Range("B") Then

Set NewRange = ?????????????


Range("NewRange").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

Range("NewRange").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub


I think I am on the right track, but need help defining NewRange which is the entire row in 'Database' where the value in column B and Sheet1!A1 are identical.


Any suggestions?

 

[Luke M]

Not sure exactly where you're wanting to copy to, but hopefully this gets you pointed in right direction?

[pre]

Sub CopyRow()
Dim i As String
Dim e As Range
Dim c As Range
'i will really be a value, not a range,
'so we don't need to "set" it to somethine
i = Worksheets("Sheet1").Range("A1").Value

'Don't really need to look at all of col B, but we'll
'use an if statement later to save time
Set e = Sheets("Database").Range("B:B")

'Look at each (c)ell within e
For Each c In e
If c.Value = "" Then
'Assumes we've reached the end of the column
Exit For
End If

If c.Value = i Then
c.EntireRow.Copy
'Where are we copying to???
'Will there be multiple records?
Range(NewRange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next c

End Sub

[/pre]

 

[mantooth29]

Thanks Luke! I can see that this is likely going to do the trick.


The range I am pasting to is actually the exact same range I am copying. The reason is because I need to clear out the formulas so when Sheet 1 is updated with new data, the previous days row does not return to blank or zero.


Actually the Paste range is the only part of the code I can't get to work. Shouldn't it go like this?


If c.Value = i Then

c.EntireRow.Copy

'Where are we copying to???

'Will there be multiple records?

Range(c.EntireRow.Copy).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End If

Next c


End Sub