VBA-copy values sheet1->sheet2, A505:A700->E2:E197 stop if cell blank [SOLVED]

[l_mirica]

The action is already in the title and I know I'm close but got stuck:

[pre]

Dim wsS As Worksheet, wsU As Worksheet
Set wsS = Sheets("sheet1")
Set wsU = Sheets("non_confid")
Dim col1 As String, col2 As String, i As Long, j As Long
Set wsS = ActiveWindow.ActiveSheet
col1 = "A"
Set wsU = ActiveWindow.ActiveSheet
col2 = "E"
For i = 505 To 700
For j = 2 To 197
If Not IsEmpty(ActiveCell.Value) Then
wsS.Range(col1 & i).Copy
wsU.Range(col2 & j).PasteSpecial xlPasteValues
End If
Next i

[/pre]
*the list is already sorted so it will end by the first blank cell

Thank you in advance!

 

[Smallman]

Hi L_mirica


From my understanding some of the cells have data some don't? If this assumption is correct then the very heart of your problem is that you want to move 195 cells of complete data from sheet1 to sheet2. Give the following a try.


'sub moveit()

sheet1.[a505:a700].specialcells(2, 23).copy sheet2.[e2]

end sub'


The above will move the data from sheet1 Col A to sheet2 Col E. It will do it nice and quickly too. Now apply this to your problem and hopefully you will get the result you are after. Trick for fresh players. Make sure the sheet object lines up so sheet1 is your originally titled "Sheet1" and sheet2 is your "Non_Confid". The method I have used above should help you in future if the names change.


It should work and it should work pretty well. Hope this helps.


Take care


Smallman

 

[Luke M]

Would this work?

[pre]

Sub CopyPaste()
Dim wsS As Worksheet, wsU As Worksheet
Dim lRow As Long

Set wsS = Sheets("sheet1")
Set wsU = Sheets("non_confid")

With wsS
'Since we stop at first blank cell, we can grab them all
'in one single operation
lRow = .Range("A505").End(xlDown).Row
.Range("A505:A" & lRow).Copy
wsU.Range("E2").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub

[/pre]

 

[l_mirica]

simply gorgeous!