Macro for copying entire row to another sheet ONLY if a value is entered in column F

[mjaeger1]

Hi All,

Please see below

I am looking to write a macro to accomplish:

- When a value is entered into column F, I would like the entire row to automatically copy to sheet2 and sheet3

-Sheet2 should be an exact copy of the row from sheet1

- On sheet3, I would like to hide columns G and H (Price and total).

Thank you so much!!!



[TABLE="width: 1116"]
<tbody>[TR]
[TD="colspan: 9"]"Easy Button" Cheat Sheet - For Budgeting Purposes Only[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]Only[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Part #[/TD]
[TD]Mfg[/TD]
[TD]Description[/TD]
[TD](Budgetary) Estimated Price[/TD]
[TD]Quantity[/TD]
[TD]Total[/TD]
[TD]MFG Stock?[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]LAN Infrastructure[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]Panels, Jacks, & Faceplates[/TD]
[/TR]
[TR]
[TD]Category 6 Infrastructure[/TD]
[TD]1111111[/TD]
[TD]A[/TD]
[TD]24 Port Category-6 110 Style Patch Panel, 1U, Angled[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111112[/TD]
[TD]A[/TD]
[TD]24 Port, 1U, Unloaded Panel, Angled[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111113[/TD]
[TD]A[/TD]
[TD]48 Port Category-6 Patch Panel, 1U, Angled[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111114[/TD]
[TD]A[/TD]
[TD]48 Port, 1U, Unloaded Panel, Angled[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111115[/TD]
[TD]A[/TD]
[TD]48 Port, 2U, Unloaded Panel, Angled[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111116[/TD]
[TD]A[/TD]
[TD]Blank Insert for Unloaded Patch Panel[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111117[/TD]
[TD]A[/TD]
[TD]Category-6 Jack, UTP[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111118[/TD]
[TD]A[/TD]
[TD]1 Port Faceplate, Single-Gang[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111119[/TD]
[TD]A[/TD]
[TD]2 Port Faceplate, Single-Gang[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111120[/TD]
[TD]A[/TD]
[TD]3 Port Faceplate, Single-Gang[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111121[/TD]
[TD]A[/TD]
[TD]4 Port Faceplate, Single-Gang[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111122[/TD]
[TD]A[/TD]
[TD]2-port Surface Mount Box (for WAPS) * Any Color*[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111123[/TD]
[TD]A[/TD]
[TD]4-port Surface Mount Box (for WAPS) * Any Color*[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD] Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD="colspan: 8"]Cable[/TD]
[/TR]
[TR]
[TD]1111111[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMR (Riser), UTP, BLUE (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111112[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMR (Riser), UTP, WHITE (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111113[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMR (Riser), UTP, GRAY (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111114[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMR (Riser), UTP, Orange (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]MTO[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111115[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMP (Plenum), UTP, BLUE (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111116[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMP (Plenum), UTP, WHITE (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111117[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMP (Plenum), UTP, GRAY (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111118[/TD]
[TD]A[/TD]
[TD]LANmark-6, CAT6, CMP (Plenum), UTP, ORANGE (1000') MIn[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]MTO[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1111111[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMR (Riser), UTP, BLUE (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111112[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMR (Riser), UTP, WHITE (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111113[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMR (Riser), UTP, GRAY (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111114[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMR (Riser), UTP, ORANGE (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]MTO[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111115[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMP (Plenum), UTP, BLUE (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111116[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMP (Plenum), UTP, WHITE (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111117[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMP (Plenum), UTP, GRAY (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111118[/TD]
[TD]A[/TD]
[TD]LANmark-1000, CAT 6E, CMP (Plenum), UTP, ORANGE (1000') Enhanced[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]MTO[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD="colspan: 8"]Copper Patch Cords[/TD]
[/TR]
[TR]
[TD]1111111[/TD]
[TD]A[/TD]
[TD]SLIM (Reduced Diameter) (.15 OD) PCORD C6 UTP 1' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111112[/TD]
[TD]A[/TD]
[TD]SLIM (Reduced Diameter) (.15 OD) PCORD C6 UTP 3' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111113[/TD]
[TD]A[/TD]
[TD]SLIM (Reduced Diameter) (.15 OD) PCORD C6 UTP 5' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111114[/TD]
[TD]A[/TD]
[TD]SLIM (Reduced Diameter) (.15 OD) PCORD C6 UTP 7' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111115[/TD]
[TD]A[/TD]
[TD]SLIM (Reduced Diameter) (.15 OD) PCORD C6 UTP 10' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111116[/TD]
[TD]A[/TD]
[TD]SLIM (Reduced Diameter) (.15 OD) PCORD C6 UTP 15' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111117[/TD]
[TD]A[/TD]
[TD]SLIM (Reduced Diameter) (.15 OD) PCORD C6 UTP 20' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1111111[/TD]
[TD]A[/TD]
[TD]SlimLine Boot-Clear Snagless PCORD C6 UTP (.225 OD) 3' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111112[/TD]
[TD]A[/TD]
[TD]SlimLine Boot-Clear Snagless PCORD C6 UTP (.225 OD) 5' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111113[/TD]
[TD]A[/TD]
[TD]SlimLine Boot-Clear Snagless PCORD C6 UTP (.225 OD) 7' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111114[/TD]
[TD]A[/TD]
[TD]SlimLine Boot-Clear Snagless PCORD C6 UTP (.225 OD) 10' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111115[/TD]
[TD]A[/TD]
[TD]SlimLine Boot-Clear Snagless PCORD C6 UTP (.225 OD) 15' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]1111116[/TD]
[TD]A[/TD]
[TD]SlimLine Boot-Clear Snagless PCORD C6 UTP (.225 OD) 20' **ANY STANDARD COLOR**[/TD]
[TD] $ 10.00[/TD]
[TD][/TD]
[TD] $ -[/TD]
[TD]Y[/TD]
[TD]Spec Sheet[/TD]
[/TR]
[TR]
[TD]Budgetary Bom Value[/TD]
[TD][/TD]
[TD="colspan: 3"]Total Budgetary BOM Value[/TD]
[TD]0[/TD]
[TD="colspan: 3"] $ - [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

try this code in the worksheet change event for sheet 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("F:F")) Is Nothing) Then
 ronno = Target.Row
   inarr = Range(Cells(ronno, 1), Cells(ronno, 9))
 With Worksheets("Sheet2")
  .Range(.Cells(ronno, 1), .Cells(ronno, 9)).Value = inarr
 End With
  inarr(1, 7) = ""
  inarr(1, 8) = ""
 With Worksheets("Sheet3")
  .Range(.Cells(ronno, 1), .Cells(ronno, 9)).Value = inarr
 End With
  
End If




End Sub

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet1 tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 7/1/18 7:20 PM EDT
If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "F").End(xlUp).Row + 1
Dim Lastrowa As Long
Lastrowa = Sheets(3).Cells(Rows.Count, "F").End(xlUp).Row + 1
r = Target.Row
Rows(r).Copy Sheets(2).Rows(Lastrow)
Rows(r).Copy Sheets(3).Rows(Lastrowa)
Sheets(3).Columns("G").Resize(, 1).Hidden = True
End If
End Sub