Two different sheet data to be pasted on one another sheet

[vishu123]

I have a excel sheet where Sheet 2 named as 'master sheet' and sheet 3 named as 'revenue data'. I want to enter the company name in cell G2 in sheet 1 which will search the same name in column F of 'Master Sheet' and paste it in sheet 1 below master header. Similary the value of G2 needs to be searched in column B of 'Revenue Sheet' and then paste it in the sheet 1 below Revenue header.


Fro example: if I write 'Moto' in cell G2 and on clicking on Submit button then Moto name should be searched in both the Master & Revenue sheet and output should be pasted in the sheet 1 below each headers.


I don't have any knowledge of macro. Can you please help me in creating macro for the same

 

[vishu123]

Master Tab has below data:

Name Group Type Year Designation Company Link Anurag abc Employee Current Associate Nokia sjdchbdjh Amit def Customer Previous None 1 Store ascvsdjcv Anil abc Customer Current None 2 Store asdcbhsd Aditya jkl Customer Previous Associate Samsung asdcbhjdc Kauti def Employee Previous Associate Samsung sdccd Abhi xyz Investor Current Associate Dell asdjhcb Kun wer Investor Current Associate Moto tyhyh Art drf Customer Current None LYF cvbn Mai xyz Investor Current Associate Moto ert <colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="16" style="width:48pt"></colgroup><tbody>
</tbody>

Revenue has below data:

Id	Supplier Name	Year 1	Year 2	Year 3	Year 4	Year 5	Web Link
11	apple	65	87	94	67	54	sdcsd
22	Dell	72	96	55	55	72	zxc
33	LG	81	53	59	88	78	ghnhn
44	Samsung Pvt	69	88	74	75	86	cgfh
55	Dell	55	81	72	84	61	hjm
66	Moto Inc	86	73	79	61	76	dryty
77	Moto	54	58	88	63	93	bfbgb
88	LYF	72	69	75	54	83	wrtt
99	Dell Ltd	54	97	58	95	62	wer
12	LYF Mobile	50	86	53	79	50	dfbgb
13	apple	82	69	89	73	64	tyh
14	Samsung	89	94	80	72	50	gfhnh
15	LG	74	90	55	95	99	sdtr

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

I want in sheet 1 if I enter Moto in G2 then it should show me data in sheet 1 like:



Under Master Tab it must show:

Name	Group	Type	Year	Designation	Company	Link
Kun	wer	Investor	Current	Associate	Moto	tyhyh
Mai	xyz	Investor	Current	Associate	Moto	ert

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

Under Revenue header it must show

Id	Supplier Name	Year 1	Year 2	Year 3	Year 4	Year 5	Web Link
66	Moto Inc	86	73	79	61	76	dryty
77	Moto	54	58	88	63	93	bfbgb

<colgroup><col width="64" span="8" style="width: 48pt;"></colgroup><tbody>
</tbody>

 

[vishu123]

Output would look like this:

Moto

<tbody> </tbody>

Master Sheet

Revenue

Name

Group

Type

Year

Designation

Company

Link

Id

Supplier Name

Year 1

Year 2

Year 3

Year 4

Year 5

Web Link

Kun

wer

Investor

Current

Associate

Moto

tyhyh

66

Moto Inc

86

73

79

61

76

dryty

Mai

xyz

Investor

Current

Associate

Moto

ert

77

Moto

54

58

88

63

93

bfbgb

<colgroup><col span="18"></colgroup><tbody>
</tbody>

 

[DanteAmor]

Try this

Change data in red by your information.

Sub [B][COLOR=#0000ff]Copy_Data[/COLOR][/B]()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  Set sh2 = Sheets("[COLOR=#ff0000]Master[/COLOR]")
  Set sh3 = Sheets("[COLOR=#ff0000]Revenue[/COLOR]")
  
  sh1.Rows("3:" & Rows.Count).Clear
  sh2.Range("A1").AutoFilter Field:=6, Criteria1:="=*" & sh1.Range("G2") & "*"
  sh3.Range("A1").AutoFilter Field:=2, Criteria1:="=*" & sh1.Range("G2") & "*"
  sh2.AutoFilter.Range.EntireRow.Copy sh1.Range("A4")
  sh3.AutoFilter.Range.EntireRow.Copy sh1.Range("A" & Rows.Count).End(xlUp)(3)
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Copy_Data) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[vishu123]

Thanks a ton DanteAmor !!! This is working for me.

Only one change I want in this - after searching the records, it should paste side- by- side data for Master and revenue sheet with one blank column as separator.

 

[vishu123]

Master sheet have 7 columns so it should be pasted from cell A to cell G

Revenue sheet have 17 columns , so it should be pasted from cell I to cell W.

Also I want above Master's pasted Data, 7 cells merged and should be named as Master with Light Green Background. Similarly, above Revenue pasted data, 15 cells merged and should be named as Revenue with light blue background.

This will be really helpful if you can share codes on the basis of this requirement.

Thanks in Advance !!!

 

[DanteAmor]

Master sheet have 7 columns so it should be pasted from cell A to cell G

Revenue sheet have 17 columns , so it should be pasted from cell I to cell W.

Also I want above Master's pasted Data, 7 cells merged and should be named as Master with Light Green Background. Similarly, above Revenue pasted data, 15 cells merged and should be named as Revenue with light blue background.

This was not in the original requirement, it is better if you create a new thread.

This will be really helpful if you can share codes on the basis of this requirement.

Thanks in Advance !!!

Try this

To leave a separation column.

Sub Copy_Data()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Master")
Set sh3 = Sheets("Revenue")

sh1.Rows("3:" & Rows.Count).Clear
sh2.Range("A1").AutoFilter Field:=6, Criteria1:="=*" & sh1.Range("G2") & "*"
sh3.Range("A1").AutoFilter Field:=2, Criteria1:="=*" & sh1.Range("G2") & "*"
sh2.AutoFilter.Range.EntireRow.Copy sh1.Range("A4")
sh3.AutoFilter.Range.CurrentRegion.Copy sh1.Range("I4")
End Sub

 

[vishu123]

Thanks a lot for you help. Closing this thread.

 

[DanteAmor]

Thanks a lot for you help. Closing this thread.

I'm glad to help you. Thanks for the feedback.