Repeat File Name for Each Row

[C_0k]

Hello,

I have combined different workbooks in one excel worksheet with Kuttols Plus.

The worksheet information is mentioned just before each range. A blank raw was also inserted in front of each range.

Is there any quick way to have the workbook name (highlighted in yellow) in front of each item as there's around 100 different range in the worksheet? Don't want to do this manually.

_Combined results(2021-11-24 175547).xlsx
	A	B	C	D	E	F	G	H
3582		Workbooks:Steel substructure Hook MW205.R.221-002_Assembly.xlsx Worksheets:PARTS LIST Range:$A$1:$F$13
3583		ITEM	QTY	PART NUMBER	DESCRIPTION	WIDTH [mm]	LENGTH [mm]
3584		1	1	Steel substructure CR Hook MW205.R.221-002	Steel substructure for hooks Case 2		557.60
3585		3	2	Female Hook C2.tube	Aluminium Profile SP0317		220.00
3586		5	2	DIN 916 - M5 x 16	Hexagon Socket Set Screw
3587		6	2	DIN 916 - M16 x 20	Hexagon Socket Set Screw
3588		2	1	Male Hook C2.tube	Aluminium Profile SP0735		156.50
3589		7	14	DIN 125 - A 13	Washer DIN 125-A - A4-70
3590		9	4	DIN 933 - M12 x 40	Hex-Head Bolt DIN 933 - A4-70
3591		8	6	DIN 934 - M12	Hex Nut DIN 934 - A4-70
3592		10	2	DIN 933 - M12 x 50	Hex-Head Bolt DIN 933 - A4-70
3593		11	2	DIN 933 - M12 x 30	Hex-Head Bolt DIN 933 - A4-70
3594		12	1	Male Hook C2.profile	Aluminium Profile SP0735		156.50
3595		13	1	ISO 8752 - 6 x 80 A	Spring-type straight pins - Slotted, heavy duty
3596
3597		Workbooks:Steel substructure Hook MW205.R.221-002_Parts list.xlsx Worksheets:PARTS LIST Range:$A$1:$H$5
3598		ITEM	QTY	PART NUMBER	DESCRIPTION	WIDTH [mm]	LENGTH [mm]	MATERIAL
3599		1	1	Steel tube MW205.R.221-002	Steel SHS 80x3mm		557.60	Steel S275
3600		2	2	Steel plate Hook L C2 MW205.R.221-002	Steel plate thk.10mm			Steel S275
3601		3	1	Steel plate Lh C2 MW205.R.221-002	Steel plate thk.10mm	100.00	220.00	Steel S275
3602		4	1	Steel plate Rh C2 MW205.R.221-002	Steel plate thk.10mm	100.00	220.00	Steel S275
3603
3604		Workbooks:Steel substructure Hook MW205.R.225-002_Assembly.xlsx Worksheets:PARTS LIST Range:$A$1:$F$13
3605		ITEM	QTY	PART NUMBER	DESCRIPTION	WIDTH [mm]	LENGTH [mm]
3606		1	1	Steel substructure CR Hook MW205.R.225-002	Steel substructure for hooks Case 2		563.50
3607		3	2	Female Hook C2.tube	Aluminium Profile SP0317		220.00
3608		5	2	DIN 916 - M5 x 16	Hexagon Socket Set Screw
3609		6	2	DIN 916 - M16 x 20	Hexagon Socket Set Screw
3610		2	1	Male Hook C2.tube	Aluminium Profile SP0735		156.50
3611		7	14	DIN 125 - A 13	Washer DIN 125-A - A4-70
3612		9	4	DIN 933 - M12 x 40	Hex-Head Bolt DIN 933 - A4-70
3613		8	6	DIN 934 - M12	Hex Nut DIN 934 - A4-70
3614		10	2	DIN 933 - M12 x 50	Hex-Head Bolt DIN 933 - A4-70
3615		11	2	DIN 933 - M12 x 30	Hex-Head Bolt DIN 933 - A4-70
3616		12	1	Male Hook C2.profile	Aluminium Profile SP0735		156.50
3617		13	1	ISO 8752 - 6 x 80 A	Spring-type straight pins - Slotted, heavy duty
3618
3619		Workbooks:Steel substructure Hook MW205.R.225-002_Parts list.xlsx Worksheets:PARTS LIST Range:$A$1:$H$5
3620		ITEM	QTY	PART NUMBER	DESCRIPTION	WIDTH [mm]	LENGTH [mm]	MATERIAL
3621		1	1	Steel tube MW205.R.225-002	Steel SHS 80x3mm		563.50	Steel S275
3622		2	2	Steel plate Hook L C2 MW205.R.225-002	Steel plate thk.10mm			Steel S275
3623		3	1	Steel plate Lh C2 MW205.R.225-002	Steel plate thk.10mm	100.00	220.00	Steel S275
3624		4	1	Steel plate Rh C2 MW205.R.225-002	Steel plate thk.10mm	100.00	220.00	Steel S275
PARTS LIST

Thanks in davance !

 

[C_0k]

This should talk for itself

 

[Fluff]

Should this start from row 2?

 

[C_0k]

Row 1. But row 2 would do the job.

 

[Fluff]

Ok, how about

Sub C_0k()
   Dim Rng As Range
  
   For Each Rng In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(, -2).Value = Rng.Offset(-1, -1).Resize(1).Value
   Next Rng
End Sub

 

[C_0k]

Just saved me couple hour of copy-pasting

 

[Fluff]

You're welcome & thanks for the feedback.