We help IT Professionals succeed at work.

How to append records to an excel worksheet and insert rows when necessary in Excel 2007

231 Views
Last Modified: 2012-05-11
Hi Experts,

In my file I have a command button that filters information from a 'Sample' worksheet in excel and copies, performs a calculation, then pastes into a 'Data' worksheet in the same workbook.  On the 'Data' worksheet, there is a header row on line 30.  I need to know how I can insert more records on the 'Data' worksheet without overwriting the header row.  Any ideas?
Comment
Watch Question

Find the next empty row after that header row and then write to it for example

LastRow = Sheets("Sheet1").Range("A" & rows.count).end(xlup).row+1

assuming that the header is in Col A.

If cell A31 is empty then you will get LastRow = 31

Simply write to that cell :)

Sid

Author

Commented:
Sid,

Please tell me where in the code below to insert that line:

Private Sub CommandButton1_Click()
Dim rng As Range
Dim cel As Range
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim r As Long
r = 3
Set ws = Sheets("HighRollers")
Set ws1 = Sheets("Data")
Set rng = ws.Range("BZ11:BZ" & ws.Cells(1000, "BZ").End(xlUp).row)

For Each cel In rng
If cel.Value = "ace" _
    Or cel.Value = "hearts" _
    Or cel.Value = "gin rummy" _
Then
    cel.EntireRow.Copy Sheets("Data").Cells(r, 1)
    cel.Offset(, -1).Copy
    Sheets("Data").Cells.Resize(, 9).SpecialCells(xlCellTypeConstants).PasteSpecial operation:=xlPasteSpecialOperationDivide
    r = r + 1
End If

Next cel
Call Z
Call P
Call Sunny
Call Cloudy
Quick question.

Does Cell A30 have anything in the header?

Sid

Author

Commented:
No, it doesn't.

Author

Commented:
Ooops, sorry...yes it does.
Which cell in Row 30 will always have a value?

Sid
ok :)
Replace

r=3 to

with

r = Sheets("Data").Range("A" & rows.count).end(xlup).row+1

Now try it.

Sid

Author

Commented:
When I did that, it copied everything under the second header row on the worksheet.  I need everything to be copied under the first header row on row 2.  The second header row starts on row 30, and if there are more than 27 rows copied over, I would like for the code to push the second header row down accordingly and not overwrite it.

Author

Commented:
I should say that it pasted everything under the second header row
Ah ok! :) Got it. A moment please.

Sid
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Sid, you are absolutely golden!! GOD BLESS YOU!!! Thank you soooo much!!!
Glad to be of help.

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.