• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

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?
0
daintysally
Asked:
daintysally
  • 8
  • 6
1 Solution
 
SiddharthRoutCommented:
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
0
 
daintysallyAuthor 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
0
 
SiddharthRoutCommented:
Quick question.

Does Cell A30 have anything in the header?

Sid
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
daintysallyAuthor Commented:
No, it doesn't.
0
 
daintysallyAuthor Commented:
Ooops, sorry...yes it does.
0
 
SiddharthRoutCommented:
Which cell in Row 30 will always have a value?

Sid
0
 
SiddharthRoutCommented:
ok :)
0
 
SiddharthRoutCommented:
Replace

r=3 to

with

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

Now try it.

Sid
0
 
daintysallyAuthor 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.
0
 
daintysallyAuthor Commented:
I should say that it pasted everything under the second header row
0
 
SiddharthRoutCommented:
Ah ok! :) Got it. A moment please.

Sid
0
 
SiddharthRoutCommented:
Try this

    Dim rng As Range, cel As Range
    Dim ws As Worksheet, ws1 As Worksheet
    Dim r As Long, HeaderRow As Long
    
    'r = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    r = 3
    HeaderRow = 30
    
    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
            If r > HeaderRow - 1 Then
                Sheets("Data").Rows(HeaderRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                HeaderRow = r
            End If
        End If
    Next cel

Open in new window


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

Sid
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now