Copy To Next Available Row

[charllie]

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.

'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

 

[Tazguy37]

Try something like this:

            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!

 

[charllie]

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

 

[charllie]

Hi Folks,

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

Thanks

 

[jindon]

What do you want to do when row 2:50 is full?

 

[charllie]

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

 

[jindon]

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

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

 

[charllie]

Hi Jindon,

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

As always, i appreciate your time.