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

Timfalcone

New Member
Joined
Jul 2, 2015
Messages
15
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Assuming the column B value is for the Master worksheet.
Code:
Sub CopyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, fAdr As String
Set sh1 = Sheets("Master")
Set sh2 = Sheets("Base")
Set fn = sh1.Range("B:B").Find("1", , xlValues, xlWhole)
    If Not fn Is Nothing Then
        fAdr = fn.Address
        Do
            fn.EntireRow.Copy sh2.Range("A" & fn.Row)
            Set fn = sh1.Range("B:B").FindNext(fn)
        Loop While fn.Address <> fAdr
    End If
End Sub
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try this in a standard module.

Howard


Code:
Option Explicit

Sub Master_Base_Copy()
Dim OneRng As Range
Dim bRow As Long
Dim c As Range

Set OneRng = Sheets("Master").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)


For Each c In OneRng
  
  If c = 1 Then
    bRow = c.Row
    c.EntireRow.Copy Sheets("Base").Range("A" & bRow)
  End If
  
Next

End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,254
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
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
 

Timfalcone

New Member
Joined
Jul 2, 2015
Messages
15

ADVERTISEMENT

I must be doing something wrong.... Developer Tab > Macros > Creat New > insert your Macro > Run and Save... Not working. Can you help.


Assuming the column B value is for the Master worksheet.
Code:
Sub CopyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, fAdr As String
Set sh1 = Sheets("Master")
Set sh2 = Sheets("Base")
Set fn = sh1.Range("B:B").Find("1", , xlValues, xlWhole)
    If Not fn Is Nothing Then
        fAdr = fn.Address
        Do
            fn.EntireRow.Copy sh2.Range("A" & fn.Row)
            Set fn = sh1.Range("B:B").FindNext(fn)
        Loop While fn.Address <> fAdr
    End If
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,254
Office Version
  1. 2013
Platform
  1. Windows
Right click sheet tab. Choose View Code. Choose "Insert" choose "Module" Then paste in your code
 

Timfalcone

New Member
Joined
Jul 2, 2015
Messages
15

ADVERTISEMENT

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?
 

Timfalcone

New Member
Joined
Jul 2, 2015
Messages
15
Sorry... One more thing.... It doesn't delete the copied row when the B column value isn't 1 or is changed.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,254
Office Version
  1. 2013
Platform
  1. Windows
Your original post did not mention deleting rows. You never mentioned wanting this to run automatic. I see your not using my script so I would not want to attempt to change JLG script. If you do not want to choose run then you can create a shape and assign the Macro to it. Or assign the Macro to a Keyboard shortcut.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Sorry... One more thing.... It doesn't delete the copied row when the B column value isn't 1 or is changed.
This statement is confusing. The row does not copy unless the '1' is in column B.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,539
Members
414,246
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top