novice working with a spredsheet
 

ok, i have a spread sheet with a list of property and a list of each
properties key numbers.

Each property has 2 or 3 or 4 copys of each key and each key is signed
out to different people at different times. i need excel to make a
worksheet i can easily access and mark who has which key when.

So far i have all the address in one colum, the key ref in another and
the number of key copies we have for each property.

BUT i am trying to get my head around an issue... if i input say 3
keys i would like to automatically generate 3 rows underneath that
property and copy the address/key ref cells so i can then input who
has what where and when.

I have found a macro that will take the number of key copies and then
make blank rows below the row but not to copy details into them

I then use a autofilter to the top of the sheet which allows me to
search for address, key ref and by location.

I dont want to get tooo complicated and i need it in excel as everyone
in the office must be able to come to my pc and access this sheet
quickly and easily.

Am i barking up the right tree & can anyone suggest a maco to copy the
details -

novice working with a spredsheet
 

Hi, from your post, I guess that you know how to insert new blank rows. The
following code snippet will help duplicate the contents of Source cell into
the Destination cell.

Dim t1, t2 as Range
set t1 = Cells( Source_Row, Source_Col )
set t2 = Cells( Destination_Row, Destination_Col )
t2.value = t1.value

I hope it helps you.

" wrote:

ok, i have a spread sheet with a list of property and a list of each
properties key numbers.

Each property has 2 or 3 or 4 copys of each key and each key is signed
out to different people at different times. i need excel to make a
worksheet i can easily access and mark who has which key when.

So far i have all the address in one colum, the key ref in another and
the number of key copies we have for each property.

BUT i am trying to get my head around an issue... if i input say 3
keys i would like to automatically generate 3 rows underneath that
property and copy the address/key ref cells so i can then input who
has what where and when.

I have found a macro that will take the number of key copies and then
make blank rows below the row but not to copy details into them

I then use a autofilter to the top of the sheet which allows me to
search for address, key ref and by location.

I dont want to get tooo complicated and i need it in excel as everyone
in the office must be able to come to my pc and access this sheet
quickly and easily.

Am i barking up the right tree & can anyone suggest a maco to copy the
details -

novice working with a spredsheet
 

i have the following code that i have found which works wonderful from
this group :

Sub InsertRows()
Dim lastRow As Long, cell As Range
'Tom Ogilvy, 2005-03-09 programming --corrected
Dim i As Long
lastRow = Cells(Rows.Count, "d").End(xlUp).Row + 1
For i = lastRow To 2 Step -1
Set cell = Cells(i, "d")
If IsNumeric(cell(0, 1).Value) Then
If cell(0, 1).Value = 1 Then
cell.Resize(cell(0, 1).Value) _
.EntireRow.Insert
End If
End If
Next i
End Sub

It takes the number i put into the ket field and inserts that many
blank rows under the row required - it does this for the entire
sheet... so all i need is to copy the data from the row then paste it
into the empty field before moving to the next row and repeating, will
see if this code you sent can be used.

thanks

novice working with a spredsheet
 

On 9 Mar 2007 04:47:58 -0800, wrote:

ok, i have a spread sheet with a list of property and a list of each
properties key numbers.

Each property has 2 or 3 or 4 copys of each key and each key is signed
out to different people at different times. i need excel to make a
worksheet i can easily access and mark who has which key when.

So far i have all the address in one colum, the key ref in another and
the number of key copies we have for each property.

BUT i am trying to get my head around an issue... if i input say 3
keys i would like to automatically generate 3 rows underneath that
property and copy the address/key ref cells so i can then input who
has what where and when.

I have found a macro that will take the number of key copies and then
make blank rows below the row but not to copy details into them

I then use a autofilter to the top of the sheet which allows me to
search for address, key ref and by location.

I dont want to get tooo complicated and i need it in excel as everyone
in the office must be able to come to my pc and access this sheet
quickly and easily.

Am i barking up the right tree & can anyone suggest a maco to copy the
details -

Post the macro that you're using the add the blank rows.

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

novice working with a spredsheet
 

Suppose the addresses are in Col A, key_ref in Col B, and key_ct in
Col C, with row1 headers. The following will insert blank rows using
key_ct and copy from A & B to the new blank rows. You can then use
Col C to insert names of persons with copies. The result has a nice
feature. If you have, say, 3 keys for an address, there are 4 rows,
with the 1st showing the number of keys and 2-4 showing any names with
copies.

Sub InsertCopy()
Dim n As Integer
Dim r As Integer
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
r = 2
Do
n = ws.Cells(r, 3)
ws.Rows(r + 1).Resize(n).Insert Shift:=xlDown
ws.Range("A" & r & ":B" & r).Copy _
ws.Cells(r + 1, 1).Resize(n, 2)
r = r + n + 1
Loop Until ws.Cells(r, 1) = ""
End Sub

Hth,
Merjet

novice working with a spredsheet
 

Post the macro that you're using the add the blank rows.

--
Dick Kusleika
Microsoft MVP-Excelhttp://www.dailydoseofexcel.com- Hide quoted text -

- Show quoted text -


Well i have posted the macro that i am using in my previous reply. but
let me explain the data layout in each of the A-D colums
A :
ContractID
(number that ref the property)
B:
Address
(property address)
C:
KeyRef
(Number tages to each key
D:
Numberofkeys
(How many keys produced)

Now i have got as far as using the number entered into NumberofKeys
and inserting that many blank rows below that specific row.
This is done for the entire worksheet one time, and any further new
properties are manually entered at the bottom of the sheet.
We have close to 1,000 properties on the list, so this is a one-shot
deal and once done wont need to be repeated BUT i would like to
understand the work behind the macro in case i need it again.

thanks for any help.

novice working with a spredsheet
 

On 9 Mar 2007 10:13:43 -0800, wrote:


Now i have got as far as using the number entered into NumberofKeys
and inserting that many blank rows below that specific row.
This is done for the entire worksheet one time, and any further new
properties are manually entered at the bottom of the sheet.
We have close to 1,000 properties on the list, so this is a one-shot
deal and once done wont need to be repeated BUT i would like to
understand the work behind the macro in case i need it again.

I've changed a few things from Tom's macro, but I've commented everything,
so hopefully you can follow it. Be sure to post back if there's something
you don't understand.

Sub InsertRows()

Dim lastRow As Long, cell As Range
Dim i As Long

'Find the last row with data in it
lastRow = Cells(Rows.Count, "d").End(xlUp).Row

'Loop (bottom to top) through the rows
For i = lastRow To 2 Step -1
'Assign a variable to the current row
Set cell = Cells(i, "d")
'Make sure the cell contains a number
If IsNumeric(cell.Value) Then
'Make sure the cell's number is greater than 1
If cell.Value 1 Then
'Insert rows below the current row
cell.Offset(1, 0).Resize(cell.Value - 1).EntireRow.Insert
'Copy the current row to the newly inserted rows
cell.Resize(cell.Value).EntireRow.FillDown
End If
End If
Next i

End Sub

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com