VBA- No form on Mac

[Polyscript]

Hi, i'm new here and trying to learn. Hoping someone can help please?

I am using a Mac with Excel and the form function is not there. I am trying to create a macro to help record information.

I want to have a table which I fill in, which I can then press "record" which would then copy that to another table on the SAME sheet. The issue I am having is that I don't know how to make the copied row go to the next available row in the other table. Sorry if that doesn't make sense.

I have recorded and assigned the macro to the button but as I said- I don't know how to copy it to the next available row, can someone help please?

Sub Record()

Range("Table1").Select
Selection.Copy
Range("Table13[Date]").Select
ActiveSheet.Paste

End Sub


Thank you in advance!

 

[Joe4]

Welcome to the Board!

Can you post some examples of your tables (the one you are entering into, and the one you want to paste on to the end of)?
I would like to see how they are structured.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

Also note. If the table you are adding this to the bottom of really is a "table", you will probably need to first add a row to the table to put it in.
You can see how to do that in VBA in this tutorial link here: Excel VBA - How to Add Rows and Columns to Excel Table with VBA Macro –

 

[Polyscript]

Hi,

thanks for the reply- sure, I have attached an image. I hope it explains what I want to do, should be simply enough but I am an idiot and a noob.

I just want the record button to copy from the table above to the table below with the next available row.

Kind Regards

 

[Joe4]

Is your Input table always EXACTLY one record long, so you will be entering on the same row every time?

 

[Polyscript]

Is your Input table always EXACTLY one record long, so you will be entering on the same row every time?

Hi Joe,

Yes that's right, it would only be one record long to record one transaction at a time to be recorded into the table below. Currently each time I click record a new link automatically gets added to the table below with the same information.

 

[Joe4]

OK, I am no expert on dealing with tables, but I think I have pieced something together that may work for you.
I am assuming (and hoping) that it works the same way in Mac as it does for Windows (as I have windows).

Based on your example, I am assuming that your first table is in Range B1:G2
and your second table starts on row 8.

Here is the code:

Sub MyRecord()

    Dim ws As Worksheet
    Dim tbl1 As ListObject
    Dim tbl2 As ListObject
    Dim cr As Long
    Dim hr As Long
    Dim tr As Long
    Dim nr As Long
   
'   Set row number of data row from first table
    cr = 2
   
'   Set row number where header of second table starts
    hr = 8

'   Set table objects
    Set ws = ActiveSheet
    Set tbl1 = ws.ListObjects("Table1")
    Set tbl2 = ws.ListObjects("Table13")
   
'   Count number of rows currently in second table
    tr = tbl2.ListRows.Count
   
'   Add a new row to the bottom of the second table
    tbl2.ListRows.Add
   
'   Copy data from first table to second table, in columns B:G
    Range(Cells(cr, "B"), Cells(cr, "G")).Copy Cells(hr + tr + 1, "B")
   
End Sub

Adjust any variable values, as needed, to fit your needs.

 

[Polyscript]

OK, I am no expert on dealing with tables, but I think I have pieced something together that may work for you.
I am assuming (and hoping) that it works the same way in Mac as it does for Windows (as I have windows).

Based on your example, I am assuming that your first table is in Range B1:G2
and your second table starts on row 8.

Here is the code:

Sub MyRecord()

    Dim ws As Worksheet
    Dim tbl1 As ListObject
    Dim tbl2 As ListObject
    Dim cr As Long
    Dim hr As Long
    Dim tr As Long
    Dim nr As Long
  
'   Set row number of data row from first table
    cr = 2
  
'   Set row number where header of second table starts
    hr = 8

'   Set table objects
    Set ws = ActiveSheet
    Set tbl1 = ws.ListObjects("Table1")
    Set tbl2 = ws.ListObjects("Table13")
  
'   Count number of rows currently in second table
    tr = tbl2.ListRows.Count
  
'   Add a new row to the bottom of the second table
    tbl2.ListRows.Add
  
'   Copy data from first table to second table, in columns B:G
    Range(Cells(cr, "B"), Cells(cr, "G")).Copy Cells(hr + tr + 1, "B")
  
End Sub

Adjust any variable values, as needed, to fit your needs.

Dude you rule!!! I was at this forever- just had to change the 2 to a 3.

Is there somewhere you can guide me to, to learn this stuff? I am watching the tiger spreadsheets guy on youtube but if you know something better let me know.

Seriously, thank you- I really appreciate it, I was really stuck.

 

[Joe4]

Excellent! You are welcome.
Glad to hear I was able to help you out, especially since I don't use a Mac and hardly ever use Tables (I usually use Microsoft Access for all my Table needs).

Quite honestly, I was able to piece this together just by doing a bunch of Google Searches on things like "Excel VBA insert row to bottom of table" and "Excel VBA counts rows in a table".
Of course, I have a pretty good VBA foundation to build on.

Years ago, I purchased an intro to Excel VBA book, which gave me a good basis, and then joined this site, and helped where I could, and read a lot of other questions that interested me to learn how to do other things. I am still constantly learning new things (and Google searches are my good friend!).

 

[Polyscript]

Excellent! You are welcome.
Glad to hear I was able to help you out, especially since I don't use a Mac and hardly ever use Tables (I usually use Microsoft Access for all my Table needs).

Quite honestly, I was able to piece this together just by doing a bunch of Google Searches on things like "Excel VBA insert row to bottom of table" and "Excel VBA counts rows in a table".
Of course, I have a pretty good VBA foundation to build on.

Years ago, I purchased an intro to Excel VBA book, which gave me a good basis, and then joined this site, and helped where I could, and read a lot of other questions that interested me to learn how to do other things. I am still constantly learning new things (and Google searches are my good friend!).

Sorry for the late reply- I did try searches but I guess I didn't put the right words together.

I should probably get VBA for dummies or something. You did pretty amazingly for someone who doesn't work with macs. I'll keep looking through topics on here to gain knowledge- it would be nice to get to a point where I can help others!!! Im going to play around with this one and see if I can copy to a different sheet.

Cheers again buddy, really appreciate it