New to VBA and needing some help pls

[vonsnapper]

So I'm trying to write some code that would allow a person to select an item off a list and then excel will automatically copy the contents of the selected cell and the adjacent cell (which contains the supply code for the item) and then automatically paste them into two new cells. This will allow me build a smaller list of things (from the large list) that I want to order for work. If I don't use the code in Bold it will automatically select both cell I click on plus the adjacent one and copy them. It will not paste them however and I must do it manually (which is much better then typing the list every week) however it would be even better if excel would automatically paste them as well. If I add the code in bold...well I had to shut excel down and restart it froze things up so bad. Any thoughts or ideas would be greatly appreciated.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = 0
If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
With ActiveCell
Range(ActiveCell, ActiveCell.Offset(0, 1)).Select
Range(Cells(.Row, .CurrentRegion.Column), _
Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)) _
.Interior.Color = vbCyan And Selection.Copy

Range("J5").Select
If IsEmpty(Target) Then
ActiveSheet.Paste
Else
Target.Offset(1, 0) = ActiveSheet.Paste
End If​

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>


End With
Application.ScreenUpdating = True
End Sub




<strike></strike>

 

[JoeMo]

Your code is triggered each time a cell is selected which causes it to run endlessly giving the appearance of freezing up. To avoid this you must disable events (specifically, disable the SelectionChange event). You can do this using the command:
Application.EnableEvents = False

placed before the first line that selects and adding

Application.EnableEvents = True

after all the selecting is completed.

Note that your code could be written in a way that avoids selecting altogether, but being new to VBA you might want to tackle that later.

 

[My Aswer Is This]

Where on your sheet will you be wanting to copy this data from?
And where do you want it pasted?

You said:

select an item off a list and then excel will automatically copy the contents of the selected cell and the adjacent cell (which contains the supply code for the item) and then automatically paste them into two new cells. ​

But you did not say where the list is. And what does adjacent cell me?
Do you mean cell to right left top or bottom

And then you said:

and then automatically paste them into two new cells. ​

What new cells?

We need specific details like column numbers and sheet names.

 

[vonsnapper]

Your code is triggered each time a cell is selected which causes it to run endlessly giving the appearance of freezing up. To avoid this you must disable events (specifically, disable the SelectionChange event). You can do this using the command:
Application.EnableEvents = False

placed before the first line that selects and adding

Application.EnableEvents = True

after all the selecting is completed.

Note that your code could be written in a way that avoids selecting altogether, but being new to VBA you might want to tackle that later.

Thank you Joe I inserted the code as you said and excel no longer "breaks". Still doesn't do quite what I want but I will continue to tinker. Much appreciated.

Knowledge is power, Power provides information; Information leads to education, education breeds wisdom, wisdom is liberation...might be a bit till I'm free; but I will keep fighting.

 

[vonsnapper]

Where on your sheet will you be wanting to copy this data from?
And where do you want it pasted?

You said:

select an item off a list and then excel will automatically copy the contents of the selected cell and the adjacent cell (which contains the supply code for the item) and then automatically paste them into two new cells. ​

But you did not say where the list is. And what does adjacent cell me?
Do you mean cell to right left top or bottom

And then you said:

and then automatically paste them into two new cells. ​

What new cells?

We need specific details like column numbers and sheet names.

First Id like to thank you for responding.

Secondly,

Currently the "Item column" starts in cell E5 and goes to E10. The "Item Code" column fills adjacent cells F5 to F10. The large list will get bigger. I'm currently just getting everything to work.
​

The way the current code works it will copy any cell I click on (that is not empty) and also automatically selects the cell to the right (This works fine). I would like it to paste the "ITEM" into J5 and then the "ITEM CODE" into K5. I understand that once I get the code to work I will have to give it some parameters so that if a person accidently clicks on a cell that is not empty but not part of the large list; it will not paste unwanted information into smaller list I'm building. Also it will paste the first selection I make into J5/K5 and then stops there.

Knowledge is power, Power provides information; Information leads to education, education breeds wisdom, wisdom is liberation...might be a bit till I'm free; but I will keep fighting.​

 

[My Aswer Is This]

You said:

Currently the "Item column" starts in cell E5​

Then you said:

The "Item Code" column​

What column is the

"Item Code" column ?


​

 

[vonsnapper]

You said:

Currently the "Item column" starts in cell E5​

Then you said:

The "Item Code" column​

What column is the

"Item Code" column ?


​

LARGE LIST					LIST I WANT TO BUILD
	ITEM	Supply Code				ITEM	SUPPLY CODE
	Sunlight	65464665A
	Bolt	234423A
	Nut	545454C
	Whiskey	45645D
	Washer	465464A
	Box of Rags	645455D
	Stuff One	4582468d
	Stuff Two	554464d
	Hammer	6546463a
	Wrench	798793a
	Pipe Wrench	654331f
	Good Floorhand	465464564A
	Nylon Rope	64654654D
	Sash Cord	466544A
	Beer	564564F
	Wrench	46546545V

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

So the list I'm picking off is E5:E10 (Item column). When I select a cell in the E5:E10 Range, it automatically copies that cell and the adjacent cell to the right F5:F10 (Item Code Column).
I would like the items I pick (by selecting a cell between E5:E10) to be automatically copied and pasted into the new list (list I want to build, starting at cells J5:K5) and then proceeding down from there as a person picks more items off the large list.