Need to Copy and paste range on last row of the same sheet.

[alleneure]

I have my code 90% working but cant figure out this last thing. I am automating a trip tracker for use throughout the company and the file will be sent out, with no team members added, to the team leads to start tracking trips. I have the current code setup to create a sheet for each new user added where the individual will insert the trips completed and the trips will automatically get filled out in monthly sheets. At the top of the sheet I have the days and the new user name getting filled out. I need to paste the range at the top of the sheet for each month to the last row of the month nut can't get it to work. I'm sure its something stupid like syntax error cause I've adapted most of this code from other sources and don't really know VBA.

I was thinking of an iRow2 but don't think it pertains to what I'm trying to do. Also cant define the cell to paste because as users are added the final row moves. I have labeled below what I have tried including a super dumbed down version of it. I have the others months not active because I'm trying to figure out one month before the others. Anything to help or to reduce the size of code and make it more efficient would be greatly appreciated.

Private Sub AddUserButton_Click()

Dim iRow1 As Long
Dim iRow2 As Long
Dim ws As Worksheet
Dim Tbl As ListObject

'Find first empty row in Users List
iRow1 = Sheets("Summary").Range("S" & Rows.Count).End(xlUp).Row + 1

'copy the data to the database, use protect and unprotect lines with your password if worksheet is protected
With Worksheets("Summary")
  .Unprotect Password:=""
  .Cells(iRow1, 19).Value = Trim(Me.txtFirstName.Value)
  .Cells(iRow1, 20).Value = Trim(Me.txtLastName.Value)
  '.Protect Password:=""
End With

'Make New Sheet for new user
Worksheets("Template").Copy Before:=Worksheets("End")
ActiveSheet.Name = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)


'Insert New user name in Month sheet rows
Sheets("Jan").Range("B1:B31") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("Feb").Range("B1:B29") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("March").Range("B1:B31") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("April").Range("B1:B30") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("May").Range("B1:B31") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("June").Range("B1:B30") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("July").Range("B1:B31") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("Aug").Range("B1:B31") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("Sept").Range("B1:B30") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("Oct").Range("B1:B31") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("Nov").Range("B1:B30") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)
'Sheets("Dec").Range("B1:B31") = Trim(Me.txtFirstName.Value) & " " & Trim(Me.txtLastName.Value)

'Copy a months days with New user name in month sheets

Sheets("Jan").Range("A1:B31").Copy Destination:=Sheets("Jan").Range("A" & Rows.Count).End(x1Up).Row + 1     'didnt work

Sheets("Jan").Select                     'didnt work
Range("A1:B31").Select                                           
Selection.Copy

iRow2 = Sheets("Jan").Range("A" & Rows.Count).End(xlUp).Row + 1                'didnt work
.Cells(iRow2, 1).Value = .Paste
ActiveSheet.Paste

'Sheets("Jan").Range("A1:B29").EntireRow.Copy Destination:=Sheets("Jan").Range("A" & RowsCount).End(x1Up).Offset(1, 0)        'didnt work

'Sheets("Jan").Range("A1:B29").EntireRow.Copy Destination:=Sheets("Jan").Range("A" & Rows.Count).End(x1Up).Offset(1, 0)       'didnt work

'Sheets("Feb").Range("A1:B30").EntireRow.Copy Destination:=Sheets("Feb").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("March").Range("A1:B30").EntireRow.Copy Destination:=Sheets("March").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("April").Range("A1:B30").EntireRow.Copy Destination:=Sheets("April").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("May").Range("A1:B31").EntireRow.Copy Destination:=Sheets("May").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("June").Range("A1:B30").EntireRow.Copy Destination:=Sheets("June").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("July").Range("A1:B31").EntireRow.Copy Destination:=Sheets("July").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("Aug").Range("A1:B31").EntireRow.Copy Destination:=Sheets("Aug").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("Sept").Range("A1:B30").EntireRow.Copy Destination:=Sheets("Sept").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("Oct").Range("A1:B31").EntireRow.Copy Destination:=Sheets("Oct").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("Nov").Range("A1:B30").EntireRow.Copy Destination:=Sheets("Nov").Range("A" & RowsCount).End(x1Up).Offset(1, 0)
'Sheets("Dec").Range("A1:B31").EntireRow.Copy Destination:=Sheets("Dec").Range("A" & RowsCount).End(x1Up).Offset(1, 0)

'Delete New user names to set as blank again
Sheets("Jan").Range("B1:B31") = ""
'Sheets("Feb").Range("B1:B29") = ""
'Sheets("March").Range("B1:B31") = ""
'Sheets("April").Range("B1:B30") = ""
'Sheets("May").Range("B1:B31") = ""
'Sheets("June").Range("B1:B30") = ""
'Sheets("July").Range("B1:B31") = ""
'Sheets("Aug").Range("B1:B31") = ""
'Sheets("Sept").Range("B1:B30") = ""
'Sheets("Oct").Range("B1:B31") = ""
'Sheets("Nov").Range("B1:B30") = ""
'Sheets("Dec").Range("B1:B31") = ""

'Clear names from the box
Me.txtFirstName.Value = ""
Me.txtLastName.Value = ""
'Focus on First name for another entry
Me.txtFirstName.SetFocus

'Rename all table the name of sheet
For Each ws In Worksheets
    For Each Tbl In ws.ListObjects
        Tbl.Name = ws.Name
        Exit For
    Next Tbl
Next ws

'Go to Summary page

   
End Sub

 

[OaklandJim]

Unfortunately without your workbook someone would have to create fake data to try to help you. Might you provide a workbook that shows what is needed? Use the link icon above the message area.

One thing see in your code is that the following won't work.

'Find first empty row in Users List
iRow1 = Sheets("Summary").Range("S" & Rows.Count).End(xlUp).Row + 1

See if this helps.

With Sheets("Summary")
       iRow1 = .Cells(.Rows.Count, "S").End(xlUp).Row + 1
End With

.Cells means all cells in worksheet (e.g., Summary in this case). .Rows.Count means the count of all rows in the worksheet named Summary. The S indicates that you want the last row in column S.

Describe with detail what these lines of code are meant to accomplish

'Copy a months days with New user name in month sheets

Sheets("Jan").Range("A1:B31").Copy Destination:=Sheets("Jan").Range("A" & Rows.Count).End(x1Up).Row + 1     'didnt work

Sheets("Jan").Select                     'didnt work
Range("A1:B31").Select
Selection.Copy

iRow2 = Sheets("Jan").Range("A" & Rows.Count).End(xlUp).Row + 1                'didnt work
.Cells(iRow2, 1).Value = .Paste
ActiveSheet.Paste

 

[OaklandJim]

I tried to understand your need without a workbook to test with. I was unable to guess what is needed. That said, what you want sounds doable, if I know more.

Ideally you post your workbook using the link icon above the message area with Dropbox, 1Drive, Google Drive etc. so someone willing to assist does not have to create a "dummy" workbook to use for coding.

 

[MARK858]

One thing see in your code is that the following won't work.

'Find first empty row in Users List
iRow1 = Sheets("Summary").Range("S" & Rows.Count).End(xlUp).Row + 1

Why won't it work?

 

[OaklandJim]

Mark858

As you imply

iRow1 = Sheets("Summary").Range("S" & Rows.Count).End(xlUp).Row + 1

does work. I jumped to an ill-formed off-the-cuff conclusion. Worse yet, my suggested approach does NOT work. I meant well.

 

[MARK858]

Worse yet, my suggested approach does NOT work

Yours works for me

Sub TEST()
Dim iRow1 As Long
With Sheets("Summary")
       iRow1 = .Cells(.Rows.Count, "S").End(xlUp).Row + 1
End With
MsgBox iRow1
End Sub

Book2
	A														O	P	Q	R	S	T
1																			xxxx
2																			xxxx
3																			xxxx
4																			xxxx
5																			xxxx
6																			xxxx
7																			xxxx
8																			xxxx
9																			xxxx
10																			xxxx
11																			xxxx
12
Summary

 

[OaklandJim]

Well, that is what happens when I post without testing. I assumed that doing the rows count first then the column letter would not work. Works just like column letter first then rows count. A surprise to me. I learned something new.

I still hope to assist with OP's request. I'm sure that if I do you'll check my work ;>

 

[MARK858]

The syntax for Cells is Cells(row number, column number [or column letter in quotes]), which is common syntax for VBA where you get the row number before the column number (for example when you use Offset or Resize it is the same order).
The option for the number on the column part makes it more flexible than using Range, other than that they both do the same job i.e. reference a single cell.

 

[MARK858]

@alleneure
Try changing

Sheets("Jan").Range("A1:B31").Copy Destination:=Sheets("Jan").Range("A" & Rows.Count).End(x1Up).Row + 1     'didnt work

to

Sheets("Jan").Range("A1:B31").Copy Destination:=Sheets("Jan").Range("A" & Rows.Count).End(xlUp).Offset(1)