Formula to get data from 1 sheet to other

[Arzan Malegamwalla]

Hi All,

I have an excel workbook with with more than 40 sheets. Sheet 1 is the main Master Sheet where I paste the data from the other sheets as and when I want 1 after the other. I have worked out a macros for the same but am looking out for a shorter way to get the data.

Sheet 1 - Master Sheet where I paste the data from other sheets
[TABLE="class: grid, width: 729"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Tour Manager[/TD]
[TD]Date Of Handover[/TD]
[TD]Date Of Loading[/TD]
[TD]Amount Handover[/TD]
[TD]Handover Type[/TD]
[/TR]
[TR]
[TD]Ms. Mithila Bandiwdekar[/TD]
[TD]26-04-2017[/TD]
[TD]26-04-2017[/TD]
[TD]4780 AUD[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Ms Rhea Serrao[/TD]
[TD]22-11-2017[/TD]
[TD]22-11-2017[/TD]
[TD]1325 AUD[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Ms. Mildred D'souza[/TD]
[TD]12-09-2017[/TD]
[TD] [/TD]
[TD]885 AUD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Mr. Mahesh Lalla[/TD]
[TD]18-08-2017[/TD]
[TD]18-08-2017[/TD]
[TD]29900 USD[/TD]
[TD]Butterfly Card[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="class: grid, width: 699"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Tour Manager[/TD]
[TD]Date Of Handover[/TD]
[TD]Date Of Loading[/TD]
[TD]Amount Handover[/TD]
[TD]Handover Type[/TD]
[/TR]
[TR]
[TD]Mr. Shyam Thanawala[/TD]
[TD]27-11-2017[/TD]
[TD]27-11-2017[/TD]
[TD]8240 EUR[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Mr. Dhananjay Mahajan[/TD]
[TD]06-12-2017[/TD]
[TD]06-12-2017[/TD]
[TD]795 EUR[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Mr. Mahesh Pratap Singh[/TD]
[TD]13-11-2017[/TD]
[TD]13-11-2017[/TD]
[TD]3320 EUR[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Mr. Sheldon Pereira[/TD]
[TD]30-09-2017[/TD]
[TD]30-09-2017[/TD]
[TD]7815 EUR[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Mr. Sujit Tayade[/TD]
[TD]01-12-2017[/TD]
[TD]01-12-2017[/TD]
[TD]700 EUR[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Mr. Arif Shaikh[/TD]
[TD]27-11-2017[/TD]
[TD]27-11-2017[/TD]
[TD]960 EUR[/TD]
[TD]Butterfly Card[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 3
[TABLE="class: grid, width: 698"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Tour Manager[/TD]
[TD]Date Of Handover[/TD]
[TD]Date Of Loading[/TD]
[TD]Amount Handover[/TD]
[TD]Handover Type[/TD]
[/TR]
[TR]
[TD]Ms Palak Singhla[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2530 USD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Ms. Prerana Thakur[/TD]
[TD]07-11-2017[/TD]
[TD]07-11-2017[/TD]
[TD]500 USD[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Ms Jigisha Patil[/TD]
[TD]25-09-2017[/TD]
[TD] [/TD]
[TD]2520 USD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Mr. Ravindra Chaure[/TD]
[TD]03-11-2017[/TD]
[TD] [/TD]
[TD]300 USD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Mr. Aniruddha Chowdhury[/TD]
[TD]02-11-2017[/TD]
[TD] [/TD]
[TD]410 USD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Mr. Parvinder Singh Bharaj[/TD]
[TD]01-12-2017[/TD]
[TD] [/TD]
[TD]735 USD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Mr. Aldrin Dias[/TD]
[TD]27-11-2017[/TD]
[TD]27-11-2017[/TD]
[TD]20850 USD[/TD]
[TD]Butterfly Card[/TD]
[/TR]
[TR]
[TD]Ms Amrutha Shetty[/TD]
[TD]31-08-2017[/TD]
[TD] [/TD]
[TD]1360 USD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Ms. Janki Patel[/TD]
[TD]31-08-2017[/TD]
[TD] [/TD]
[TD]3095 USD[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Mr Pranav Samant[/TD]
[TD]25-05-2017[/TD]
[TD] [/TD]
[TD]4710 USD[/TD]
[TD]Cash[/TD]
[/TR]
</tbody>[/TABLE]


The macros used is different for each sheet and as below:
Sub Sheet 2()
Dim rngSource As Range
Dim rngTarget As Range
Dim iRow As Integer

Set rngSource = Worksheets("Sheet 2").Range("A2:E7")
iRow = Worksheets("Sheet 1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Set rngTarget = Worksheets("Sheet 1").Range("A" & iRow)
rngSource.Copy Destination:=rngTarget
End Sub


I have tried using the formula "+Sheet 2!A2 but it copies data from cell A2 of Sheet 2 and that to only values whereas I need to copy the data from range of cells as it is.

Appreciate your help.

 

[lhartono]

So you want to copy from those 40 sheets to Master Sheet ("Sheet1") in single macro call?

 

[Arzan Malegamwalla]

I want to copy data from one sheet at a time on the master sheet.

 

[Arzan Malegamwalla]

I have tried using formula +Sheet 2!A2 but it copies only the data from A2 from 2nd sheet and that to without formatting. I am looking out for such a formula that will allow me to copy data range and with the same formating.

 

[Arzan Malegamwalla]

I want to copy data from one sheet at a time on the master sheet. I have tried using formula +Sheet 2!A2 but it copies only the data from A2 from 2nd sheet and that to without formatting. I am looking out for such a formula that will allow me to copy data range and with the same formating.