Transpose info from different Worksheets

VLV98

New Member
Joined
Jan 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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 VBA that 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 advise please?

Thank you :)
 

Attachments

  • 1.PNG
    1.PNG
    19.3 KB · Views: 2
  • 2.PNG
    2.PNG
    23.7 KB · Views: 2
  • 3.PNG
    3.PNG
    27 KB · Views: 2

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
693
Office Version
  1. 365
Platform
  1. Windows
Please, try this code.
VBA Code:
Sub ReplicateData()
 Dim ws As Worksheet, LR As Long
  For Each ws In Worksheets(Array("Veggies", "Fruit", "Frozen"))
   With ws
    LR = .Cells(Rows.Count, 1).End(3).Row
     Union(.Cells(LR, 1), .Cells(LR, 3).Resize(, 2)).Copy Sheets("List").Cells(Rows.Count, 2).End(3)(2)
     Sheets("List").Cells(Rows.Count, 1).End(3)(2) = Date
   End With
  Next ws
End Sub
 

VLV98

New Member
Joined
Jan 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,923
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Transpose data automatically
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
693
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The last row of data from "veggies, fruit and frozen" into the first available row in the tab "list".
I wrongly understood that you wanted to replicate the data from that three sheets, and not fom just the active one. Sorry for that.
Try this instead.
VBA Code:
Sub ReplicateData()
 Dim LR As Long
  With ActiveSheet
   LR = .Cells(Rows.Count, 1).End(3).Row
    Union(.Cells(LR, 1), .Cells(LR, 3).Resize(, 2)).Copy Sheets("List").Cells(Rows.Count, 2).End(3)(2)
    Sheets("List").Cells(Rows.Count, 1).End(3)(2) = Date
  End With
End Sub
 

VLV98

New Member
Joined
Jan 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Osvaldo,

Thank you so much, this works beautifully. Sorry I didn't properly explain my self the first time.

Could you please let me know what to do if I need more specific cells to be copied as well?

Thank you!
I wrongly understood that you wanted to replicate the data from that three sheets, and not fom just the active one. Sorry for that.
Try this instead.
VBA Code:
Sub ReplicateData()
 Dim LR As Long
  With ActiveSheet
   LR = .Cells(Rows.Count, 1).End(3).Row
    Union(.Cells(LR, 1), .Cells(LR, 3).Resize(, 2)).Copy Sheets("List").Cells(Rows.Count, 2).End(3)(2)
    Sheets("List").Cells(Rows.Count, 1).End(3)(2) = Date
  End With
End Sub
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
693
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi.

This bit of code ~~~> .Cells (LR, 3) .Resize (, 2) is copying from columns C and D, so if the other cells you want to copy are adjacents, say columns E, F and G, then just change to
.Cells (LR, 3) .Resize (, 5).

Or if the other cells are not adjacents, then include them in the Union. Ex:, to include columns G and K:
Union (.Cells (LR, 1), .Cells (LR, 3) .Resize (, 2), .Cells (LR, 7), .Cells (LR, 11)). Copy
Remembering that in both cases the copied cells will be pasted into adjacent cells on sheet List.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,923
Office Version
  1. 365
Platform
  1. Windows
@VLV98 maybe you missed this part of post#4
If you have posted the question at more places, please provide links to those as well.
Please provide the other links
 

VLV98

New Member
Joined
Jan 13, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Please, try this code.
VBA Code:
Sub ReplicateData()
 Dim ws As Worksheet, LR As Long
  For Each ws In Worksheets(Array("Veggies", "Fruit", "Frozen"))
   With ws
    LR = .Cells(Rows.Count, 1).End(3).Row
     Union(.Cells(LR, 1), .Cells(LR, 3).Resize(, 2)).Copy Sheets("List").Cells(Rows.Count, 2).End(3)(2)
     Sheets("List").Cells(Rows.Count, 1).End(3)(2) = Date
   End With
  Next ws
End Sub
Hi Osvaldo,

Thank you so much for this!

By any chance can you help me with a code to find changes into an excel sheet and then update the data into the "Master" file?

All of the options I can find copy the full column or full row and I need some specific dat only.

Thank you :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,923
Office Version
  1. 365
Platform
  1. Windows
@VLV98
Please do not ignore requests from the Moderators. Supply the links to the other sites where you have asked this question.
 

Forum statistics

Threads
1,144,237
Messages
5,723,182
Members
422,482
Latest member
MacSapper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top