New but learning

[vonsnapper]

Hello. So I've been working on a little project for a friend and it is almost done. The one problem that still remains is I would like excel to paste the contents of the cell the user selects into multiple sheets. However in the second sheet (Fhand) the contents simply keeps getting pasted into the same cell (whichever cell the cursor is on) rather then the next blank cell in the J column.

I was also curious if a person would be better served by using declared variables and potentially some kind of array?

The purpose of the spreadsheet itself is to allow the user to select items off a larger lists and excel will automatically copy and paste them into smaller list. Any thoughts/advice would be appreciated.

'This code is designed so the user can select items off the list.
'The chosen item and its supply code in the adjacent cell will be
'Copied and pasted into a new smaller list

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = 0

'If the user selects a cell that is empty the subroutine will exit

If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False

'This section of code selects the adjacent cell to the one user
'Clicks and formats them to a cyan color as well as copies the
'Contents of the cell

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
Application.EnableEvents = False

'This section of code selects the next available cell in
'Column J and pastes the item and supply code into the cells

Range("J65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.EnableEvents = True

End With

Application.ScreenUpdating = True

With Worksheets("Fhand")
Application.EnableEvents = False
Range("J65536").End(xlUp).Offset(1, 0).Select
Worksheets("Fhand").Paste
Application.EnableEvents = True
End With

Application.ScreenUpdating = True

End Sub

 

[JonXL]

What happens if you replace this:

Range("J65536").End(xlUp).Offset(1, 0).Select

... with this?

Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).Select

 

[Fluff]

You need a . at the start of the line in red as shown

With Worksheets("Fhand")
   Application.EnableEvents = False
  [COLOR=#ff0000] .Range("J65536").End(xlUp).Offset(1, 0).Select[/COLOR]
   .Paste
   Application.EnableEvents = True
End With

 

[steve the fish]

You wouldnt want to select presuming Fhand is not the activesheet.

 

[Fluff]

Excellent point steve

The code should be

With Worksheets("Fhand")
   Application.EnableEvents = False
   .Range("J65536").End(xlUp).Offset(1, 0).PasteSpecial
   Application.EnableEvents = True
End With

 

[vonsnapper]

Awsome thanks guys it works great. I was curious as to the significance of the "." . Also I was wondering if you knew of a good site for VBA keywords where a person can find them as well as the difference between things like "Paste" versus "PasteSpecial". Thanks again.