How do I copy and paste a (variable sized) list from one worksheet to another?

[kbj-engineer]

Hi,

I am trying to build a workbook where operators will enter finished good numbers and time they were completed. At the end of the shift, I want to create 2 buttons that will automatically copy and paste this info into two other worksheets (command button for each)
- I want the "Transfer Build Groups" button to copy and paste A1:An (however long list is) to the MasterList file. This list will be populated each day, so it needs to find the last row and paste there each time
- I want the "Transfer Takt times" button to copy and paste data into the TAKT Data tab. This will also need to find the last row and paste columns A1:Cn on that tab.
- Lastly, I want the "Clear List" button to remove all the info in A2:Cn and only have column headers left.

This is the code I have so far, but I keep getting the error "

Private Sub CommandButton3_Click() - "Transfer Build groups" command


End Sub

Private Sub CommandButton4_Click() "Transfer TAKT Time" command

Range("A1:A50").Select
Selection.Copy
Sheets("Master List").Select

Sub CurrentRegion()
Dim rg As Range
Set rg = shData.Range("A1").CurrentRegion

rg.Select

Dim lastrow As Long
lastrow = rg.Rows(rg.Rows.Count).Row


ActiveSheet.Paste

End Sub



Private Sub CommandButton5_Click()

End Sub

 

[kbj-engineer]

This is an Access forum but many members are multi-lingual and so you will probably get a suitable Excel solution. However, this task is more suited to a database application. Have you considered using Access rather than Excel as a way to capture data entry? It will solve a lot of your problems and make unnecessary some of the work you are expecting the operators to perform.

Hi Pat. Thank you for pointing that out, I thought the forum was just VBA lol.
But I do love the idea of using an Access for this problem; I hadn't considered that.
Could you walk me through general steps how to set it up with basic code?

 

[Gasman]

I tend to use the macro recorder to get the basics, then amend to suit.
Plenty of links around showing how to find last row relative to a column.