Insert a row to the end of a named range if count of cells on another sheet > 30

thefordness · Nov 20, 2013

'So, I have a user entering items on a sheet, I have vba adding these items to another sheet where everything
'is formatted and sorted by item type (the user does not see this process)
'What I'm having trouble with is getting the code to count the items of each type, then if there are more
'then 30 of that type, to insert a row into the range that the item will be written to. (this second part is
'done with a different sub)

Sub(insertifmorethan30) 'Add a row to the end of a named range if count of cells on another sheet > 30
dim listofitems as range
dim testitem as range
dim t1 as integer 'there will be a variable declared for each item type
t1 = 0

For Each type In listofitems
Select Case type
Case Is = "testitem"
t1 = t1 + 1
If t1 > 30 Then
With
[listarea] ‘named range where items entered the other sheet
.Rows(1).EntireRow.Insert
.Resize(.Rows.Count + 1, .Columns.Count).Name = .Name.Name
End With
'insert row under last appliances item in trade sheet
End If
'end select 'there will be a case for each item type

'I have tried this code as well as multiple other ways to do this and I am pulling out my hair trying to get
'it to work. I have had it insert nothing at all, countless object errors, insert rows continuously until I
'break... etc. Can someone help with a working model for this?
'note that this insert needs not to overwrite other named ranges on the sheet

Insert a row to the end of a named range if count of cells on another sheet > 30

thefordness

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

Insert a row to the end of a named range if count of cells on another sheet > 30

thefordness

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock