MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I'm at a loss

Posted by Jim on October 30, 2000 11:26 AM

This is what i need to happen, open UserForm31 on
sheet 26, view the bound rows and columns in UserForm31
through it's ListBox, Dbl click on selected row in
ListBox to copy and paste to Sheet1 @ find next blank
down. This works fine from a UserForm to a ActiveSheet.
My problem is, i don't know how to tell it
to copy and paste to an "inactive sheet" Sheet1
My code:
Option Explicit
Dim Rng As Range
Dim RW As Integer
Dim Dest As String

Private Sub ListBox1_DblClick(ByVal Cancel As MsForms.ReturnBoolean)
'RW = the list box index = selected row
RW = ListBox1.ListIndex
Range("Sheet7!B6:J6").Offset(RW, 0).Copy
'Find net Blank Row
'Now Paste
ActiveCell.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End Sub

Private Sub SelectNextBlankDown()
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Application.EnableEvents = False
End Sub

Posted by Ivan Moala on October 31, 2000 12:13 AM

A number of options;
1) Activate the sheet then use paste on Activesheet
2) have a look @ online help for Paste and its
othe option eg. Destination

Hope this gets you going