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
SelectNextBlankDown
'Now Paste
ActiveCell.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End Sub

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



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


Jim
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

Ivan