M
Matt P.
Hello
What I'm trying to do is when a user inserts data at the
end of a list on one sheet, depending on the number of
rows that were inserted, a summary sheet has the same
number of rows inserted at a certain point. What I want
to happen is when ever the user inserts data the summary
sheet will insert that number of rows and then select and
autofill down the number of rows that were inserted.
Below is my vb. There is a listbox that has the sheet
names that have the lists that data will be inserted to.
Sheets(ListBox1.ListIndex + 1).Select
Unload Me
lastrow = ActiveSheet.Cells
(Rows.count, "A").End(xlUp).Row 'Sets the numeric value of
the last row initially
ActiveSheet.ShowDataForm 'Show the dataform
Range("A1").Select
newlastrow = ActiveSheet.Cells
(Rows.count, "A").End(xlUp).Row 'Sets the numeric value of
the last row after data is inserted into the list
lstrowdiff = newlastrow - lastrow 'lstrowdiff
stores the value of the number of rows inserted
'*******************************
selectedtab = ActiveSheet.Name 'The current
active sheet
selectupdate = selectedtab & " Summ1" 'The
current sheet to be updated
Select Case lstrowdiff
Case Is = 0
Case Is >= 1
Sheets(selectupdate).Select'selects the
summary sheet that will be updated
Range("Jan04" &
selectedtab).Select 'Selects a cell with a defined name
of "Jan04"&selectedtab
Dim num As Long
'loops to insert the number of appropriate
rows
For num = 1 To lstrowdiff
Selection.EntireRow.Insert
Next num
'This is where I would like to do the autofill.
'Criteria & selectedtab below is actually a defined name
that contains the selection that would be selected and
then drug down.
'What I want to know how to do is correctly specify the
Destination and then reset the Criteria & selectedtab
defined name range to the newly inserted data.
'Jan04... is a cell in A8 for example and that is always
the right below where rows are inserted. Criteria &
selectedtab always originally starts out as A5:H5. I want
to autofill up to Jan04... where the newly inserted rows
stop. And then reset the defined name Criteria.... to
lets say A5:H10. I want this to happen without the user
having to do anything but insert data onto the original
list on the original sheet. At this point the Jan04... is
reset to A11.
Range("Criteria" & selectedtab).Select
Selection.AutoFill Destination:=Range
("Criteria" & selectedtab & ":Jan04" & selectedtab),
Type:=xlFillDefault
Range("Criteria" & selectedtab & ":Jan04"
& selectedtab).Select
End Select
Range("A4").Select
Thank you for your help.
What I'm trying to do is when a user inserts data at the
end of a list on one sheet, depending on the number of
rows that were inserted, a summary sheet has the same
number of rows inserted at a certain point. What I want
to happen is when ever the user inserts data the summary
sheet will insert that number of rows and then select and
autofill down the number of rows that were inserted.
Below is my vb. There is a listbox that has the sheet
names that have the lists that data will be inserted to.
Sheets(ListBox1.ListIndex + 1).Select
Unload Me
lastrow = ActiveSheet.Cells
(Rows.count, "A").End(xlUp).Row 'Sets the numeric value of
the last row initially
ActiveSheet.ShowDataForm 'Show the dataform
Range("A1").Select
newlastrow = ActiveSheet.Cells
(Rows.count, "A").End(xlUp).Row 'Sets the numeric value of
the last row after data is inserted into the list
lstrowdiff = newlastrow - lastrow 'lstrowdiff
stores the value of the number of rows inserted
'*******************************
selectedtab = ActiveSheet.Name 'The current
active sheet
selectupdate = selectedtab & " Summ1" 'The
current sheet to be updated
Select Case lstrowdiff
Case Is = 0
Case Is >= 1
Sheets(selectupdate).Select'selects the
summary sheet that will be updated
Range("Jan04" &
selectedtab).Select 'Selects a cell with a defined name
of "Jan04"&selectedtab
Dim num As Long
'loops to insert the number of appropriate
rows
For num = 1 To lstrowdiff
Selection.EntireRow.Insert
Next num
'This is where I would like to do the autofill.
'Criteria & selectedtab below is actually a defined name
that contains the selection that would be selected and
then drug down.
'What I want to know how to do is correctly specify the
Destination and then reset the Criteria & selectedtab
defined name range to the newly inserted data.
'Jan04... is a cell in A8 for example and that is always
the right below where rows are inserted. Criteria &
selectedtab always originally starts out as A5:H5. I want
to autofill up to Jan04... where the newly inserted rows
stop. And then reset the defined name Criteria.... to
lets say A5:H10. I want this to happen without the user
having to do anything but insert data onto the original
list on the original sheet. At this point the Jan04... is
reset to A11.
Range("Criteria" & selectedtab).Select
Selection.AutoFill Destination:=Range
("Criteria" & selectedtab & ":Jan04" & selectedtab),
Type:=xlFillDefault
Range("Criteria" & selectedtab & ":Jan04"
& selectedtab).Select
End Select
Range("A4").Select
Thank you for your help.