LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 195
Default Help with copy destination

I want to use the following to delete a UserForm designated name and then
copy a default value from another sheet into one of the resulting empty
cells:
Sub Delete_Member()
Dim Old_Member As String, cols As Long, rng As Range, c As Range
frmDelMem.Show
Old_Member = UCase$(frmDelMem.tbOldName.Text)
If Old_Member = "" Then Unload frmDelMem: Exit Sub
Unload frmDelMem
Application.ScreenUpdating = False
cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(22, 1), Cells(22, 1).End(xlDown))
For Each c In rng
If c = Old_Member Then
c.EntireRow.SpecialCells(xlCellTypeConstants).Clea rContents
Sheets(3).Range("A1").Copy rng(c.Row, cols)'<-- here's the problem
End If
Next c
rng.Resize(, cols).Sort Key1:=rng, Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

It works to a point. The correct values in the correct row are deleted, but
the cell copied from sheets(3) winds up in the wrong place. For example, if
I designate a name in the UserForm that sits at A26, Row 26 is cleared, but
the range from sheets(3) winds up in E47 instead of E26.

If I use Sheets(3).Range("A1").Copy rng(c, cols), it winds up in E21
If I use Sheets(3).Range("A1").Copy rng(1, cols), it winds up in E22

Please fix it!

--
David
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using copy and destination formatting Jeff S.[_2_] Excel Discussion (Misc queries) 6 August 18th 09 11:24 AM
VBA .copy destination jerredjohnson Excel Discussion (Misc queries) 2 July 18th 06 11:18 PM
copy destination values Pete[_23_] Excel Programming 1 March 22nd 05 04:40 PM
Range.Copy (Destination) Garry Douglas Excel Programming 3 January 3rd 05 03:02 PM
Selection.Copy Destination:= Mike Fogleman Excel Programming 3 January 10th 04 02:18 AM


All times are GMT +1. The time now is 02:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"