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]

I am using your code now.

I have been trying to add some code unsuccessfully.

I am looking to add an If 0 leave blank or clear line to code.

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 2).Value = "1" Then
Sheets("Base").Rows(i).Value = Rows(i).Value
End If
Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I don't understand. If 0 leave blank or clear line to code. Do you mean if column "B" is empty clear all data in that row?

I am using your code now.

I have been trying to add some code unsuccessfully.

I am looking to add an If 0 leave blank or clear line to code.

 

[Timfalcone]

How about this. 1 would mean the line is populated with the data from the master sheet. 0 would mean line is either deleted (if previously filled) of left blank.

I don't understand. If 0 leave blank or clear line to code. Do you mean if column "B" is empty clear all data in that row?

 

[My Aswer Is This]

I'm sorry Tim but we are up to 13 post now on this and none of us can understand what your wanting. In your original post you said "I know this is simple" and it probable is the hard part is understanding what you want. Maybe you should start over and spell out in detail what you want.

How about this. 1 would mean the line is populated with the data from the master sheet. 0 would mean line is either deleted (if previously filled) of left blank.

 

[L. Howard]

I'm sorry Tim but we are up to 13 post now on this and none of us can understand what your wanting. In your original post you said "I know this is simple" and it probable is the hard part is understanding what you want. Maybe you should start over and spell out in detail what you want.

To add to My Answer Is This' post on re-clarification... Include what/how the additional page fits in.

Ok... I have it working. But... I have to choose run for it to take effect. Also, I am going to modify this for additional page... Can I just paste code after sub end?

The row copied to sheet Base is the same row it came from on Master. Other sheets may or may not have same row numbers? So if you copy row 5 from two sheets, where do these row go on destination sheet.

And, no you do not add code after the End Sub, it must be in the Sub.

Howard

 

[Timfalcone]

Ok... I apologize for not being clearer.

Lets try this again, and I will even give a little definition behind what I am trying to do. What I am doing is creating a course curriculum from a Master sheet. (So my worksheets look like this.."Master"/Base/Standard/Advanced/Custom)

The "MasterSheet" Contains all of the courses but is the same exact lay out of the following work sheets It just has blank lines with a header. ....4 columns B/C/D/E (Correspond with Sheet names B = Base C =Standard/ECT) (Not that it matters but I have conditional formatting on to change the color of the Cell when the correct number is in the box (#1-4) are my coded columns in which tell me whether I want to sent the data to the corresponding sheet. I like it this way because the Master is color coded showing what is available and where in the curriculum.

So the idea here is that I can send Data from a master to fill in additional pages. But, also When there is edits to the master ( for example Class001 is in row 10 and was once required for BASE and now it is not) I just want to change the 1 in column B to 0 on row 10 of the master and have all the data deleted from the row 10 on the base worksheet.

What I was figuring is that With a little help from you guys I could modify the code to work with additional columns and worksheets after I got column B to work correctly.


I hope this sounds better... All of your help is very much appreciated..... Thanks.

 

[Timfalcone]

Also...

Forget about Automatic... I will just set it up to use Ctrl R

 

[JLGWhiz]

I'll take one more shot at it.

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.Range("B" & c.Row).ClearContents
    End If
Next
End Sub

 

[Timfalcone]

Close... But all the contents of the the row on the "Base" page need to be cleared not just the colored column... "B"

 

[JLGWhiz]

Close... But all the contents of the the row on the "Base" page need to be cleared not just the colored column... "B"

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