VB Help with code to copy selected rows to specified cells in another sheet.

[Excel_Assis]

Hi
Could I please ask help from someone with VB code.
The VB below should by clicking in the cell column H next to the row of data needed then copy the data to a another sheet at this point it is sheet6 at a specified start cell. There are several source sheets and the selected data from these sheets needs to be copied to sheet6 at a specified starting cell.

At present when the cell is clicked the tick font is made visible meaning the row selected and then viewing the destination sheet. only the first cell of data from source sheet is shown and if two rows are selected from source sheet only the last selected row first cell is shown. Meaning the copy overwrites the previous and does not increment by one row.





Source Sheet = Cost1

B C D E F G H

Cost1						Select Required
Description	Type	Min # of Units		Cost/Unit	Total
unit 1	Unit 1 type	2		$ 30.00	$ 60.00	a
unit 2	Unit 2 type	0		$ 5.00	$ -
unit 3	Unit 3 type	3		$ 20.00	$ 60.00	a
unit 4	Unit 4 type	0		$ 5.00	$ -
unit 5	Unit 5 type	0		$ 10.00	$ -
				Total	$ 120.00

Select and Copy Code


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H8:H12")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If


'/// Below code will copy Selected items to sheet6

'Copy Items to sheet6 and start at selected cell and incrament by 1 row.
If Not Intersect(Target, Range("H8:H12")) Is Nothing Then
Select Case Target.Value
Case "a"
Cells(Target.Row, 1).Copy Destination:=Sheet6.Range("D10").End(xlUp).Offset(1, 0)
End Select
End If

End Sub

[/CODE]

 

[kvsrinivasamurthy]

Try. Change this line

Cells(Target.Row, 1).Copy Destination:=Sheet6.Range("D10").End(xlUp).Offset(1, 0)

as

Cells(Target.Row, 1).Copy Destination:=Sheet6.Range("D"  & rows.count).End(xlUp).Offset(1, 0)

 

[Excel_Assis]

Try. Change this line

Cells(Target.Row, 1).Copy Destination:=Sheet6.Range("D10").End(xlUp).Offset(1, 0)

as

Cells(Target.Row, 1).Copy Destination:=Sheet6.Range("D"  & rows.count).End(xlUp).Offset(1, 0)

Hi I thank you for your reply however it does not work dew to the cell a D is a merged cell. This is row 1, The destination cell must start at D10 to G10 and then Auto increment to D11 to G11 then auto increment to D12 to G12 and so on.

 

[Excel_Assis]

The following is what happens on the destination sheet6.

Inventory_Xfer (1).xls
	D	E	F	G	H
9	Description	Type	Unit	Cost	TOTAL
10	unit 3				$0.00
11					$0.00
12					$0.00
13					$0.00
14					$0.00
15					$0.00
16					$0.00
17					$0.00
18					$0.00
19					$0.00
20	TOTAL				$0.00
Sys_Ser_Quote

Cell Formulas
Range	Formula
H10:H19	H10	=F10*G10
H20	H20	=SUM(H10:H19)

Following is the Source sheet10.

Inventory_Xfer (1).xls
	A	B	C	D	E	F		H
5	Cost1							Select Required
6
7	Description	Type	Min # of Units		Cost/Unit	Total		Select Required
8
9	unit 1	Unit 1 type	2		$ 30.00	$ 60.00
10	unit 2	Unit 2 type	0		$ 5.00	$ -
11	unit 3	Unit 3 type	3		$ 20.00	$ 60.00		a
12	unit 4	Unit 4 type	0		$ 5.00	$ -		a
13	unit 5	Unit 5 type	0		$ 10.00	$ -
14					Total	$ 120.00
Cost_1

Cell Formulas
Range	Formula
F9:F13	F9	=SUM(C9*E9)
F14	F14	=SUM(F9:F13)

 

[kvsrinivasamurthy]

What are the values required from D10 to G10.
As per your code
Cells(Target.Row, 1) is copied to D10.

 

[Excel_Assis]

What are the values required from D10 to G10.
As per your code
Cells(Target.Row, 1) is copied to D10.

the target should read

	unit1	unit 1 type	2​	30	60.00
					0.00
					0.00
					0.00
					0.00
					0.00
					0.00
					0.00
					0.00
					0.00
				IMPLEMENTATION TOTAL	$60.00​

 

[kvsrinivasamurthy]

Try

Cells(Target.Row, 1).resize(1,4).Copy Destination:=Sheet6.Range("D"  & rows.count).End(xlUp).Offset(1, 0)

 

[Excel_Assis]

Try

Cells(Target.Row, 1).resize(1,4).Copy Destination:=Sheet6.Range("D"  & rows.count).End(xlUp).Offset(1, 0)

Thank you for your reply however this solution tries to copy the data to row 1 of destination sheet.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VB Help with code to copy selected rows to specified cells in another sheet.
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[kvsrinivasamurthy]

I don't understand what you want.
Try.

selection.resize(1,4).Copy Destination:=Sheet6.Range("D"  & rows.count).End(xlUp).Offset(1, 0)