Prev: Finicky EssVRetrieve
Next: Auto Fill Text Box
From: jat on 16 Dec 2009 18:14
i need a few lines of code to determine the active rows in columns A and B
and copy from one work book to another.

i kind of understand the loop, but not a lot.

i need to select/copy the active cells in workbook one (always Columns A and
B) and paste them in the next blank row in Column A/B of workbook two.

i know that to find the next blank cell where to paste is the following:

Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

help would be appreciated,

jatman

From: marcus on 16 Dec 2009 20:51
Hi Jatman

This uses the Autofilter to copy the cells with data in Column A over
to another workbook and places them at the bottom of the used range in
this workbook. Change to suit.

Take care

Marcus

Option Explicit

Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim lw As Integer
Dim lr As Integer

Set wb1 = ActiveWorkbook
Set wb2 = Workbooks("BOOK3")'Change to your workbook name
lr = Range("A" & Rows.Count).End(xlUp).Row
lw = wb2.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Columns(1).AutoFilter Field:=1, Criteria1:="<>"
Range(Cells(2, 1), Cells(lr, 2)).Copy wb2.Sheets("Sheet1").Range("A" &
lw)

End Sub
 | 
Pages: 1
Prev: Finicky EssVRetrieve
Next: Auto Fill Text Box