It seems u need to copy data from 1 file to another skipping blank an
also to paste data of next file after current row pasted.
If yes, then assign 1 cell (for e.g.: A1) in your workbook-Data t
start your current row position to paste.
Now Try this..
Code
-------------------
Sub Import_Data()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myCopyRow, myPasteRow, i
Set ws1 = Workbooks("Collect.xls").Sheets("test") 'set sheets
Set ws2 = Workbooks("Data.xls").Sheets("data")
myCopyRow = ws1.Range("B65536").End(xlUp).Row 'store value for rng used in col-B
myPasteRow = ws2.Range("A1").Value
'Paste skipping blank
With ws1
For i = 2 To myCopyRow
If .Cells(i, 2).Value < 0 Then
ws2.Cells(myPasteRow, 2).Value = .Cells(i, 2).Value
myPasteRow = myPasteRow + 1 'increase
End If
Next i
End With
End Sub
-------------------
Do not forget to enter some value manually in workbook-data in cell-A
for first time before triggering this macro..
Cheers!!!
ilya
--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...fo&userid=2396
View this thread:
http://www.excelforum.com/showthread.php?threadid=47499