Help needed with code to count & insert rows

[mplees]

Hi,

Hope someone can help me out with this. In my workbook on sheet 1, I have a list of jobs & part numbers. On sheet 2 I have headings on alternate rows.

What I am trying to do is when I add data to sheet 1, I copy the inputted data & insert it into sheet 2. Difficult to explain, and I cannot post a sample sheet until I get home, but basically, the layout is:

Sheet 1

A B
1 574999
2 123456
3 456123
4 674556
5 654321
6 574998
7 650987


and so on. The first problem is that the number of items is variable - there could be 1 item or 30 items. The constant is that the job number is always in column A. On sheet 2 I have:

A B
1 =Sheet1!A1
2
3 = next job number on sheet 1
4

and so on. Does anyone have thoughts as to how this could be acomplished? The other important thing is that the formatting on sheet 2 needs to be retained when the rows are inserted.

Hope someone can help me out - all assistance gratefully received as always.

Regards,

Mark

 

[mplees]

Hi,

After much head scratching, I cobbled together the following code:

Private Sub CommandButton1_Click()
Dim vRng1 As Range, vRng2 As Range, vRng3 As Range, vRng4 As Range
Dim vCell1 As Long, vCell2 As Long, vCell3 As Long, vCell4 As Long
Dim vCell5 As Long, vCell6 As Long

Set vRng1 = Sheets("WIP data").Cells(Rows.Count, "B").End(xlUp)
Set vRng2 = Sheets("WIP data").Cells(Rows.Count, "A").End(xlUp)
Set vRng3 = Sheets("Workload").Cells(Rows.Count, "A").End(xlUp)


vCell1 = vRng1.Row
vCell2 = vRng2.Row
vCell3 = vCell1 - vCell2 + 1
vCell4 = vRng3.Row + vCell3
vCell5 = vRng3.Row

Sheets("Workload").Activate
    ActiveSheet.Range("A" & vCell5 & ":A" & vCell4).EntireRow.Insert shift:=xlDown
Sheets("WIP data").Activate
    Range(vRng2.Address & ":" & vRng1.Address).Copy
Sheets("Workload").Activate
    ActiveSheet.Range("A" & vCell5).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Range("A" & vCell5 & ":J" & vCell5).Select
    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
    Set vRng4 = Sheets("Workload").Cells(Rows.Count, "B").End(xlUp)
    vCell6 = vRng4.Row + 2
    ActiveSheet.Range("A" & vCell6).Select
End Sub

....probably very inefficient, has too many variables, etc., but it seems to work ok!

Regards,

Mark