Help with copy destination

  • Thread starter Thread starter David
  • Start date Start date
D

David

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).ClearContents
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!
 
Thanks for trying to help. I plugged in your offering and now the copied
range ends up in A21 :(
 
I kept tinkering.

Here's a line that works, keeping in mind I want Sheets(3).Range("A1") to
end up in Range(last column, same row) as the deleted name.

Sheets(3).Range("A1").Copy Range(Cells(c.Row, cols), Cells(c.Row, cols))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top