Posts by nitroxhaha

correction

as for the above code

it did exactly what it was coded to do just not what i needed it to do as it permanently stops the table 3 line add because the job cost is a permanent line

i since removed this line all together ( grey line at bottom of table 2 )

changed the code to look for a blank "" instead of " job cost" at the bottom of table 2

Sub Add_New_Line_To_Table_3()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'only if last line in Table 2 is not JOB COST in column A

Dim ws As Worksheet

Set ws = ActiveSheet

If Sheet3.Cells(Rows.Count, 1).End(xlUp) = "" Then MsgBox "No Line added ": Exit Sub

ws.ListObjects("Table3").ListRows.Add

End Sub

this now properly stops the addition of a new line on table 3 IF and only if i have a blank row on Table 2 at the bottom

so i permanently need the table to always have a blank row at the bottom

so to this effect i need to come up with an event triggered macro to add a new row to the table 2 anytime the last row of current table is populated

this will then allow the (add line ) code for table 3 to properly perform its function

this would then eliminate the need for the ( ADD LINE Code i have for the sheet MAster ESR Data

appreciate your insight into the errors your referencing

im no pro at this by far

as for the above code

it did exactly what it was coded to do just not what i needed it to do as it permanently stops the table 3 line add because the job cost is a permanent line

i since removed this line all together

changed the code the look for a blank "" instead of " job cost"

this now properly stops the addition of a new line on table three IF i have a blank row on Table 2 at the bottom

so to this effect i need to come up with an event triggered macro to add a new row to the table 2 anytime the row is populated

this will then allow the (add line ) code for table 3 to properly perform its function

how to change VBA macro to add line on Table 3 only if last line in Table 2 is not JOB COST in column A

current " line add " code in use on WS - ESR Master Invoice Data for Add New Line Button

Sub Add_New_Line_To_Table()

Dim ws As Worksheet

Set ws = ActiveSheet

ws.ListObjects("Table3").ListRows.Add
End Sub

this works absolutely perfectly

thank you sooooooo much sir

this is were i struggle as i'm trying to work with VBA at a level well above my knowledge base

i'm taking an online course for this but my current needs exceed my learning plan

long way around to saying how do you change your code to copy everything from the selected Row and then only delete the content not the format of the cells in the row F:H only

so its 50% perfect as it does insert a line below the intended line selected by double clicking a cell in Column 2 ( column B )

it is a complete blank line as apposed to coping all the data from Column A:N then deleting the content only not the format of Column F:H for the given row selected

your help and simplification of the Macro was most appreciated

Any help on this would be most appreciated as im still new to the world of VBA

Hello,

I have a worksheet with a table with data information on the the following columns A:H. I need to assign a macro to the Worksheet that will perform the following events: once the user selects a cell from column B and double clicks in that cell, the macro inserts a new row bellow the selected row with:

1) same values in columns A:N of selected row

2) Delete values (only keep formats) in columns F:H

I found in this forum the following macro that was great, but I do not know how a can get the macro to run inside the table. i feel like i need to reference the (Table 3 ) but cant figure out where in the statement to properly put it

1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
2. If ActiveCell.Column = 1 Then
3. 'David McRitchie, 2007-09-07 insrtrow.htm on double-click
4. '-- will copy more often than Extend Formulas and Format (tools option)
5. Cancel = True
6. Target.EntireRow.Copy
7. Cells(Target.Row + 1, 1).EntireRow.Insert
8. Cells(Target.Row + 1, 1).EntireRow.Select
9. ActiveSheet.Paste
10. Application.CutCopyMode = False
11. On Error Resume Next
12. '-- customize range for what cells constants can be removed --
13. Intersect(Selection, Range("F:H")).SpecialCells(xlConstants).ClearContents
14. On Error GoTo 0
15. Else
16. End If
17. End Sub