Copy some data from one Sheet to another and create a list

[VLV98]

I need to copy data form certain cells in different sheets in an excel to another sheet. The last row of data from "veggies, fruit and frozen" into the first available row in the tab "list". Only some information of it, not the whole row.

So far I got a to move into the fist blank row but not sure how to select the last row form the other tabs to copy.

Also would be nice to be able to automatically input date into when the item is added into the "list" tab.

Can anyone please help?

Thank you

[SamT]

Sub MakeList()
Dim Sht as Worksheet
Dim Listing as Worksheet '"List" is a VBA Keyword. I won't use Keywords as Variable Names.
Set Listing = Sheets("List")

For each Sht in Sheets
If Not Sht Is Listing then
With Listing
.Cells(Rows.Count, "A").End(xlUp).Offset(1) = Date
.Cells(Rows.Count, "B").End(xlUp).Offset(1) = Sht.Cells(Rows.Count, "A").End(xlUp)
.Cells(Rows.Count, "C").End(xlUp).Offset(1) = Sht.Cells(Rows.Count, "C").End(xlUp)
.Cells(Rows.Count, "D").End(xlUp).Offset(1) = Sht.Cells(Rows.Count, "D").End(xlUp)
End With
End If
Next
End Sub



That only works for very simple examples like yours. It is not suitable for more complex systems. It not very easily expanded. If you are wanting to extract Data from several Worksheet Data Bases, there are much better systems

[VLV98]

That works, amazingly! Thank you.

Is it possible to get only the last one added from each tab? Because every time I run it now it adds all the last ones instead of the new added.

Thank you!

[VLV98]

Hi,


Thank you for you help. But I've seen to run into some issues.


I am trying to get copy some cells from the last row of each different sheet but separately, like after a specific command from each sheet.


What I found is that now it copies all of the last rows from each sheet, ending up with duplicates in the Master.


Any guide on how to create a command to include in a button in each sheet that copies certain cells from the last row only in from that sheet?


Keeping the date in the first cell.


Thank you :)

[SamT]

I am trying to get copy some cells from the last row of each different sheet but separately, like after a specific command from each sheet.
What I found is that now it copies all of the last rows from each sheet, ending up with duplicates in the Master.

Told ya so in my first post.

Instead of making us write new code a dozen times while you slowly work up to what you want... You need to let us know exactly what you are finally trying to accomplish.

At least this time you provided a much better example. Thank you for that... Now... which Client Columns correlate to Master Columns "Contact Business Unit" and "Account Name"?


We can, of course teach you how to add Command Buttons to a sheet, but someone would need to do that for every Client sheet, existing and new. If you give us a step by step description of how the client sheets are edited, we can probably come up with a better system. Don't forget to mention what happens when a Status is changed.



Some People here at VBAExpress like to work on constantly expanding systems. I hate it, I want to know everything before I start so I can do everything at one time. If one of the ones who like constantly expanding systems checks in, I will leave it to them. Don't feel bad about my attitude, I'm always cranky like this, it isn't you. Just keep helping us help you.

[Fluff]

cross posted https://www.mrexcel.com/board/threads/transpose-info-from-different-worksheets.1172259/
amongst other sites

[VLV98]

Hello Sam T,

you are absolutely correct. I didn't expect anybody to do the work for me, I just needed help with bits I got stuck into but I notice that this is way away of what I can do.

In the Template I had created I had included a button in each client tab that would run the procedure and move data into Master, but would only copy the last line of each one into the first available line the Master file, meaning that all edits would be lost and only work for new clients.

The file will work with each new client being added into their correspondent tab with the information, and then be updated as needed. For example how many have completed so far or the current service (selected from dropdown option) and then costs will be calculated based on a rate for each service and the amount expected to be completed and actually completed.

Does this make it more clear? Apologies for all the inconvenience.

Thank you,

VLV

[VLV98]

Hello,

Sorry, please see below:

Transpose data automatically (excelforum.com) (https://www.excelforum.com/excel-programming-vba-macros/1350689-transpose-data-automatically.html#post5522993)