Copy To Next Available Row

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
986
Hi Folks,

I am using the folllowing code to copy and paste information from textboxes contained in a userform to a new row on a worksheet.

Before pasting the information a new row is inserted.

I would like to make a small change to this and would appreciate some help.

Instead of inserting a new row i would like it to paste to the next availble row.

For Example:

  • If row 2 already contained information from a job then go to row 3 and paste there.
    But If row 3 also contained
information from a job then go to row 4 and paste there.

Can this be done and if so how?

Here is my code i am using so far.

Code:
'This section transfers the new information entered to Shift Managers Daily Shift Report
            Dim DSR As Worksheet
                Set DSR = Workbooks("Shift Manager.xls").Worksheets("Daily Shift Report")
                    
                    DSR.Rows("2:2").Insert Shift:=Down 'Insert Row
                    
                    DSR.Range("A2") = Me.DateTextBox
                    DSR.Range("B2") = Me.ShiftTextBox
                    DSR.Range("D2") = Me.OrderNoTextBox
                    DSR.Range("E2") = Me.CatalogueTextBox
                    DSR.Range("F2") = Me.ConfigurationComboBox
                    DSR.Range("G2") = Me.CasHrsTextBox
                    DSR.Range("H2") = Me.TempHrsTextBox
                    DSR.Range("I2") = Me.PermHrsTextBox
                    DSR.Range("J2") = Me.TotalHrsTextBox
                    DSR.Range("K2") = Me.TotalDiscsTextBox
                    DSR.Range("C2") = Workbooks("Team Leader.xls").Worksheets("Team Leader Screen").Range("F6") 'Shift Pattern


Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try something like this:

Code:
            Dim DSR As Worksheet
                Set DSR = Workbooks("Shift Manager.xls").Worksheets("Daily Shift Report") 
                    With DSR
                        Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Me.DateTextBox
                        Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Me.ShiftTextBox
                        Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Me.OrderNoTextBox
                        Range("D" & Rows.Count).End(xlUp).Offset(1).Value = Me.CatalogueTextBox
                        Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Me.ConfigurationComboBox
                        '...
                    End With

Hope that helps!
 
Upvote 0
Hi Taz,

Thanks for the reply.

That works brilliantly. However there was a part i overlooked. Further down the page i have some text etc in rows 51 which will always be there. So what is happening is that it is putting the information into row 52.

Is there a way around this so it only places the information in rows 2 to 50.

Sorry to be a pain.

Thanks
 
Upvote 0
Hi Folks,

Is there anyway out there who could assist me further with this. I think Taz is probably sound asleep by now.

Thanks
 
Upvote 0
Hi Jindon,

How are you.

It should never fill all those rows so never gave it a thought. The average is about 39 rows so i went to 50 to cover mysellf. So basically 50 should be enough.

Thanks
 
Upvote 0
If cell A1 always has value then change

Range("A" & Rows.Count).End(xlUp).Offset(1)

To

Range("A1").End(xlDown).Offset(1)

and others as well

NO GOOD!!

sorry, you can get the 1st available cell like this

Code:
Dim r As Range
Set r = Columns(1).Find("", Range("a1"), , xlWhole)
If Not r Is Nothing And r.Row < 51 Then
    r.Value = Me.DateTextBox
    r.Offset(, 1).Value = Me.ShiftTextBox
    r.Offset(, 3).Value = Me.OrderNoTextBox
    r , Offset(, 4).Value = Me.CatalogueTextBox
    r.Offset(, 5).Value = Me.ConfigurationComboBox
End If
 
Upvote 0
Hi Jindon,

Thanks very much for your help. Below is the finished code and its all working perfectly.

As always, i appreciate your time.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top