Want to create macro...help required

Closed
Umesh_Shinde Posts 4 Registration date Sunday May 18, 2014 Status Member Last seen May 21, 2014 - May 19, 2014 at 11:00 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 22, 2014 at 10:43 AM
Dear All,

I have two sheet one containing first sheet containig below columns
1)customer name
2)customer type
3)customer tel no.
4)customer resident address

and other sheet containing same field but in different column.
1)customer type
2)customer name
3)customer resident address
4)customer tel no.

I want first to copy data from first sheet with header than append and add second sheet data in respective / correct columns.

Please help me in that case for creation of macro in excel so that after clicking one function key i run that macro and found all data in one sheet.

Regards,
Umesh Shinde
Phone Number deleted by moderator
Related:

3 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 20, 2014 at 12:57 AM
suppose sheet is like this

customer name a g l
customer type s h p
customer tel no. d j o
customer resident address f k i

seslect whole data copy (ontol+c)-go to sheet 2 select A pastespecial-transposse-ok

is this what you wat
0
Umesh_Shinde Posts 4 Registration date Sunday May 18, 2014 Status Member Last seen May 21, 2014
May 20, 2014 at 06:24 AM
Dear Venkat,

I have two sheet in one workbook with same column name but in different column i want to copy first sheet data and than append second sheet data as in first sheet if A1 cell contains customer name and cusotmer type in cell B1 and in second sheet if A1 cell contains customer type and customer name in cell B1.

I want to create one macro program which first copy entire data from first sheet and than also copy data and append data from sheet 2 but in same manner A column contains customer name and B column contains customer type.

Regards,
Umesh Shinde
Phone Number deleted by moderator
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 20, 2014 at 11:53 AM
Hi Umesh Shinde,

Why do you want macro for this?

It seems pretty straight forward to me:
Copy Sheet 2 Column B and paste below data of Sheet 1 Column A.
Copy Sheet 2 Column A and D and paste below data of Sheet 1 Column B.
And finally copy Sheet 2 Column C and paste below data of Sheet 1 Column D.

Or am I misunderstanding you?

Best regards,
Trowa
0
Umesh_Shinde Posts 4 Registration date Sunday May 18, 2014 Status Member Last seen May 21, 2014
May 20, 2014 at 01:58 PM
Dear TrowaD,

I have two sheet one containing first sheet containig below columns
1)customer name (Sheet1 A Column)
2)customer type (Sheet1 B Column)
3)customer tel no. (Sheet1 C Column)
4)customer resident address (Sheet1 D Column)

and other sheet containing same field but in different column.
1)customer type (Sheet2 A Column)
2)customer name (Sheet2 D Column)
3)customer resident address (Sheet2 C Column)
4)customer tel no. (Sheet2 B Column)

I want one macro which collate data from sheet 1 and 2 in sheet 3 copy first data from sheet 1 and after that copy data from sheet 2
1)customer name (Sheet3 D Column)
2)customer type (Sheet3 B Column)
3)customer tel no. (Sheet C Column)
4)customer resident address (Sheet3 A Column)

Please help me in that case for creation of macro in excel so that after clicking one function key i run that macro and found all data in one sheet3 in same workbook.

Regards,
Umesh Shinde
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 22, 2014 at 10:43 AM
Hi Umesh,

Here is the code. Do check the sheet references on lines 4 and 11.

Sub RunMe()
Dim lRow As Long

Sheets("Sheet1").Select
lRow = Range("A1").End(xlDown).Row

Range("A2:A" & lRow).Copy Sheets("Sheet3").Range("D2")
Range("B2:C" & lRow).Copy Sheets("Sheet3").Range("B2")
Range("D2:D" & lRow).Copy Sheets("Sheet3").Range("A2")

Sheets("Sheet2").Select
lRow = Range("A1").End(xlDown).Row

Range("A2:A" & lRow).Copy Sheets("Sheet3").Range("B1").End(xlDown).Offset(1, 0)
Range("B2:B" & lRow).Copy Sheets("Sheet3").Range("C1").End(xlDown).Offset(1, 0)
Range("C2:C" & lRow).Copy Sheets("Sheet3").Range("A1").End(xlDown).Offset(1, 0)
Range("D2:D" & lRow).Copy Sheets("Sheet3").Range("D1").End(xlDown).Offset(1, 0)
End Sub

And by function key you mean short cut key? Hit Ctrl + F8 to display available macro's, select the macro "RunMe" and click on options to apply short cut.

Best regards,
Trowa
0