Dear Sir, I Need a solution for problem Like when i click the Cells

[Mohamedazees]

Dear Sir,
The Solution I need for when I click a any cells in Column "E,F,G to P" I need to copy and paste specific cells to another sheet for an example if I click cell "E18" I need to copy the cell value of B18 & E3 (Row 3 is my Headers) into another Sheet2 range A2 & A3. like wise if I clicks cell F18 I need to Copy & Paste Cells F18 & F3 to the Said Range ( Sheet2 Range A2 & A3) Since this range is my criteria's for my advance filter. Please ref. the attached Image that denotes the Green color cells are clickable cells and the yellow color cells need to be copy and paste to the above said range.
Thanks in Advance for your Solutions.

 

[Crystalyzer]

Not sure if you need to extend the range down further than what is in the picture. If you do, I have pointed out where in the code you should increase the row number. I assume this is for a fiscal year so the number of columns will not change. The solution relies on the Worksheet_BeforeDoubleClick event so you will have to DOUBLE CLICK a cell in the green shaded area for the column and row headers to be copied to Sheet2.

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3					Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22
4
5
6
7
8
9
10
11
12
13
14
15
16
17		Cash Paid Out
18		Rental Deposits
19		Retail Store - LB Road
20		Retail Store - Velacherry
21		Retail Store - Anna Nagar
22		Dark Store - Porur
23		Dark Store - Tambaram
24		Dark Store - Mogappair
Sheet1

Book8
	A
1
2	Retail Store - Anna Nagar
3	4/1/2021
4
Sheet2

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim colHeader As String, rowHeader As String
    Dim ws As Worksheet
  
   'Change the 24 in this line to how ever many rows you need  Target.Row <= 24
    If Target.Row >= 18 And Target.Row <= 24 And _
        Target.Column >= 5 And Target.Column <= 16 Then
      
        colHeader = Cells(3, Target.Column)
        rowHeader = Cells(Target.Row, 2)
      
        Set ws = Sheets("Sheet2")
      
        With ws
            .Range("A2") = rowHeader
            .Range("A3") = colHeader
        End With
  
    End If

End Sub

 

[Mohamedazees]

I am sorry the my excepted result is not achieved in this code I fell I am not explained my problem proper way, now here with attached my mini sheets for both Sheet 1 & Sheet2 with details requirements

Sheet1:

Test copy Row & colum.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Expense Head	April	May	June	July	August	September	October	November	December	January	February	March
3	Rent	426	554	371	231	528	410	646	267	320	713	666	390
4	Depsosits	207	749	647	740	710	650	251	594	580	443	739	542
5	Assets	695	677	261	379	706	444	647	368	535	504	577	748
6	Laiblities	510	390	674	539	470	480	421	435	327	299	424	712
7	Credits	437	349	401	667	693	607	283	685	364	475	553	226
8	Debits	279	458	722	496	323	654	407	536	221	221	411	618
9	Value	386	423	406	508	624	745	613	490	527	435	476	746
10	Salary	238	376	209	570	452	478	325	324	387	755	573	692
11	Convetance	326	227	309	547	714	468	418	434	597	288	216	444
12	Depsosits	259	763	617	605	238	592	252	448	598	345	212	700
13	Assets	250	330	585	467	233	650	418	270	261	410	560	361
14	Laiblities	698	215	327	753	270	360	391	351	757	351	313	496
15	Credits	321	541	616	402	378	456	419	286	207	607	654	224
16	Debits	474	622	512	586	437	634	648	714	323	718	476	612
17	Value	496	222	716	671	721	678	717	376	256	364	633	426
18	Salary	307	557	732	441	433	504	419	288	563	291	678	505
19	Convetance	575	531	733	587	441	399	575	466	259	688	352	609
20
21							Month	Expense Head
22							Cell A2	Cell B2
23	Example	1	If I Click "B4"		Result in Sheet2 =		April	Depsosits
24	Example	2	If I Click "C6"		Result in Sheet2 =		May	Laiblities
25	Example	3	If I Click "D8"		Result in Sheet2 =		June	Debits
26	Example	4	If I Click "E9"		Result in Sheet2 =		July	Value
Sheet1

Cell Formulas
Range	Formula
B3:M19	B3	=RANDBETWEEN(200,765)
G23	G23	=B2
H23	H23	=A4
G24	G24	=C2
H24	H24	=A6
G25	G25	=D2
H25:H26	H25	=A8
G26	G26	=E2

Sheet2:

Test copy Row & colum.xlsm
	A	B
1	Month	Expense Head
2	April	Depsosits
Sheet2

 

[Crystalyzer]

You're right, your first explanation was NOTHING CLOSE to what you requested in the second post and my code did EXACTLY what you asked for.

Hopefully your second explanation is closer to what you want because the code below does EXACTLY WHAT you asked for in THIS ONE.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim colHeader As String, rowHeader As String
    Dim ws As Worksheet, intsct As Range
    
    Set intsct = Intersect(Target, ActiveSheet.Range("DblClkRng"))
    
    If Not intsct Is Nothing Then
        colHeader = Cells(2, Target.Column)
        rowHeader = Cells(Target.Row, 1)
        
        Set ws = Sheets("Sheet2")
        
        With ws
            .Range("A2") = colHeader
            .Range("B2") = rowHeader
        End With
    
    End If

End Sub

 

[Crystalyzer]

Forgot to mention, you need to create a dynamic (or static) range name call "DblClkRng" that incorporates ANYWHERE you want to be able to double click and have the data populate Sheet2 for the code to work.

Mohamedazees 20210815.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Expense Head	April	May	June	July	August	September	October	November	December	January	February	March
3	Rent	221	341	224	610	596	578	276	718	525	530	407	513
4	Depsosits	728	264	518	642	564	342	503	562	379	739	397	517
5	Assets	641	523	327	250	480	473	465	349	598	459	633	735
6	Laiblities	368	397	585	481	442	534	464	551	234	407	694	710
7	Credits	441	660	376	208	380	244	280	561	749	205	440	394
8	Debits	695	455	262	347	543	683	698	323	276	517	762	471
9	Value	621	736	206	639	296	515	441	724	243	585	507	341
10	Salary	348	645	254	201	290	632	728	361	371	332	760	497
11	Convetance	209	269	249	460	711	655	221	599	376	580	230	273
12	Depsosits	456	213	435	720	691	583	273	719	686	697	509	307
13	Assets	231	549	277	531	250	701	521	239	359	687	733	311
14	Laiblities	740	526	413	688	312	655	347	246	546	526	625	583
15	Credits	362	419	716	719	678	414	313	625	600	677	488	309
16	Debits	701	251	547	709	325	738	285	300	542	527	208	424
17	Value	518	617	535	234	686	334	506	541	237	740	555	292
18	Salary	523	246	239	737	715	722	203	276	295	481	471	351
19	Convetance	724	734	570	670	629	454	283	255	412	404	618	491
20
Sheet3

Cell Formulas
Range	Formula
B3:M19	B3	=RANDBETWEEN(200,765)

 

[Mohamedazees]

How to Say Perfect excellent and no more words to say you solved my problem. Thanks of Lots

 

[Crystalyzer]

Glad it worked out!