Want to create macro...help required [Closed]

Report
Posts
4
Registration date
Sunday May 18, 2014
Status
Member
Last seen
May 21, 2014
-
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
-
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

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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
Posts
4
Registration date
Sunday May 18, 2014
Status
Member
Last seen
May 21, 2014

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
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
4
Registration date
Sunday May 18, 2014
Status
Member
Last seen
May 21, 2014

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
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!