PDA

View Full Version : Solved: Copy and pasting ONLY selected cells



Klartigue
09-19-2011, 11:13 AM
I have copied and pasted some data into the attached worksheet using the code:

Sub CopyAndPaste()
Dim LastRow As Long

Windows("Volume worksheet.xlsx").Activate

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2:I2").Resize(.Range("A2").End(xlDown).Row - 1).Copy

Windows("Broker Volume Master.xlsx").Activate

.Paste Workbooks("Broker Volume Master.xlsx").ActiveSheet.Range("A2:I2").End(xlUp).Offset(1)

Application.Run "BLPLinkReset"
End With
End Sub

However, I only want to paste the selected data (A1 to A23). But when its pasted, all these N/A and brus values show up in columns A and E respectively. I dont know why they are showing up. Can I alter my macro to get them to go away?
Thanks for the help!!

Klartigue
09-19-2011, 11:30 AM
Oh wait, I got it. I did this to get rid of the N/A in column A.

Sub Blanks()
' Fill N/A and brus with nothing

Dim rng As Range
Set rng = Range("A2:E2" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cell In rng
If Application.IsNA(cell.Value) = True Then cell.Value = ""
Next cell
End Sub


I tried to do this to get rid of the brus in column E buts its not working..hmm

Sub Blank2()
Dim rng As Range
Set rng = Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
For Each cell In rng

If cll.Value = "brus" Then cll.Value = ""
Next cell
End Sub

Klartigue
09-19-2011, 11:39 AM
I got it to work!!

Sub Blank2()
' Fill brus with nothing

Dim cll As Range, rng As Range
Set rng = Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
For Each cll In rng
If cll.Value = "brus" Then cll.Value = ""
Next cll

End Sub