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

[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

 

[Pat Hartman]

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.

 

[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?

 

[Pat Hartman]

Could you walk me through general steps how to set it up with basic code?

When working with a platform that builds applications based on databases, we start with defining our table structure. Then we create queries and forms. Then code and finally reports. So, in your case, you are a long way from the code phase. You have posted only a tiny part of your process but, it is enough for you to get an idea of how you could use Access.

 

[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.