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?
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?
ASKER
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.Resiz
r = r + 1
End If
Next cel
Call Z
Call P
Call Sunny
Call Cloudy
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.Resiz
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
Does Cell A30 have anything in the header?
Sid
ASKER
No, it doesn't.
ASKER
Ooops, sorry...yes it does.
Which cell in Row 30 will always have a value?
Sid
Sid
ok :)
Replace
r=3 to
with
r = Sheets("Data").Range("A" & rows.count).end(xlup).row+
Now try it.
Sid
r=3 to
with
r = Sheets("Data").Range("A" & rows.count).end(xlup).row+
Now try it.
Sid
ASKER
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.
ASKER
I should say that it pasted everything under the second header row
Ah ok! :) Got it. A moment please.
Sid
Sid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sid, you are absolutely golden!! GOD BLESS YOU!!! Thank you soooo much!!!
Glad to be of help.
Sid
Sid
LastRow = Sheets("Sheet1").Range("A"
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