Little more advice on this code
 

Hi all Dave Peterson helped me by writing this code but I need it to do a
little more and I cant seemto work it out.

What the code below does isshows the meeting that are coming up and copies
the information to another worksheet which I will use another way. But at
the moment it only copies the first cell.

The list's rowsource is a2:e65536 so I probably need to have the code copy
the whole line.

Any ideas would be appreciated.

Here is the code
Private Sub CommandButton1_Click()
Dim destCell As Range

With Worksheets("Popup")
Set destCell = .Columns.End(xlUp).Offset(1, 0)
End With

With ListBox1
If .ListIndex -1 Then
destCell.Value = .List(.ListIndex)
End If
End With
Unload Me
messagebox.Show

End Sub
Thanks again

Greg

You changed a bit of the code.

This won't work.
Set destCell = .Columns.End(xlUp).Offset(1, 0)

So you have a listbox that has 5 columns in it. And you want to copy each of
the 5 columns to the cell?

Something like this might work:

Option Explicit
Private Sub CommandButton1_Click()
Dim destCell As Range
Dim iCtr As Long

With Worksheets("Popup")
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Me.ListBox1
If .ListIndex -1 Then
For iCtr = 1 To .ColumnCount
destCell.Offset(0, iCtr - 1).Value _
= .List(.ListIndex, iCtr - 1)
Next iCtr
End If
End With

Unload Me

End Sub

Do you really have almost 64k rows in that listbox?

Greg B wrote:

Hi all Dave Peterson helped me by writing this code but I need it to do a
little more and I cant seemto work it out.

What the code below does isshows the meeting that are coming up and copies
the information to another worksheet which I will use another way. But at
the moment it only copies the first cell.

The list's rowsource is a2:e65536 so I probably need to have the code copy
the whole line.

Any ideas would be appreciated.

Here is the code
Private Sub CommandButton1_Click()
Dim destCell As Range

With Worksheets("Popup")
Set destCell = .Columns.End(xlUp).Offset(1, 0)
End With

With ListBox1
If .ListIndex -1 Then
destCell.Value = .List(.ListIndex)
End If
End With
Unload Me
messagebox.Show

End Sub
Thanks again

Greg

--

Dave Peterson

You've got a reply at one of your other posts.

Greg B wrote:

Hi all Dave Peterson helped me by writing this code but I need it to do a
little more and I cant seemto work it out.

What the code below does isshows the meeting that are coming up and copies
the information to another worksheet which I will use another way. But at
the moment it only copies the first cell.

The list's rowsource is a2:e65536 so I probably need to have the code copy
the whole line.

Any ideas would be appreciated.

Here is the code
Private Sub CommandButton1_Click()
Dim destCell As Range

With Worksheets("Popup")
Set destCell = .Columns.End(xlUp).Offset(1, 0)
End With

With ListBox1
If .ListIndex -1 Then
destCell.Value = .List(.ListIndex)
End If
End With
Unload Me
messagebox.Show

End Sub
Thanks again

Greg

--

Dave Peterson

Whoops. I didn't notice that the message was cross posted.

Dave Peterson wrote:

You've got a reply at one of your other posts.

Greg B wrote:

Hi all Dave Peterson helped me by writing this code but I need it to do a
little more and I cant seemto work it out.

What the code below does isshows the meeting that are coming up and copies
the information to another worksheet which I will use another way. But at
the moment it only copies the first cell.

The list's rowsource is a2:e65536 so I probably need to have the code copy
the whole line.

Any ideas would be appreciated.

Here is the code
Private Sub CommandButton1_Click()
Dim destCell As Range

With Worksheets("Popup")
Set destCell = .Columns.End(xlUp).Offset(1, 0)
End With

With ListBox1
If .ListIndex -1 Then
destCell.Value = .List(.ListIndex)
End If
End With
Unload Me
messagebox.Show

End Sub
Thanks again

Greg

--

Dave Peterson

--

Dave Peterson