New to Excel Macros. Paste row from Master to existing worksheet in same row based upon # value.

[Timfalcone]

Hello,

First off let me apologize for posting this. I searched the site and could not find a working solution for my problem. I have tried to modify a few with no luck.

What I am trying to do.

I want to copy a row from a Worksheet (Master) to a worksheet (Base) based upon a number value in column. (B) of that row. # value = 1

Row must be copied over to same row on Base worksheet.

I know this is easy.. but can't figure it out ..

Thanks

 

[Timfalcone]

Sub Base1()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Master")
Set sh2 = Sheets("Base")
For Each c In sh1.Range("B2", sh1.Cells(Rows.Count, 2).End(xlUp))
If c.Value = 1 Then
With sh1
sh1.Range(c, .Cells(c.Row, Columns.Count).End(xlToLeft)).Copy sh2.Range("B" & c.Row)
End With
Else
sh2.c.EntireRow.ClearContents
End If
Next
End Sub

Getting an error... that's the code I ran.. c.Entirerow.Clearcontents gets highlighted.

Sub CopyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Master")
Set sh2 = Sheets("Base")
For Each c In sh1.Range("B2", sh1.Cells(Rows.Count, 2).End(xlUp))
    If c.Value = 1 Then
        With sh1
            .Range(c, .Cells(c.Row, Columns.Count).End(xlToLeft)).Copy sh2.Range("B" & c.Row)
        End With
    Else
        sh2.c.EntireRow.ClearContents
    End If
Next
End Sub

 

[JLGWhiz]

sh2.c.EntireRow.ClearContents

Should be

sh2.Rows(c.Row).ClearContents

My senility is showing.

 

[Timfalcone]

It works...Thanks!!

So quick question. (Rows.Count, 2) What does the 2 stand for... I am just figuring out your code. The rest makes complete sense.

 

[JLGWhiz]

It works...Thanks!!

So quick question. (Rows.Count, 2) What does the 2 stand for... I am just figuring out your code. The rest makes complete sense.

The two is the column number. Rows.Count takes it to the bottom row of the worksheet, the End(xlUp) then takes it upward to the first occupied cell in the specified column, in this case column 2.

Regards, JLG