Appending a file using VBA

[buffalo]

Appending a file using VBA

Hi,

Suppose I have two files. Q:\BLOTTER and Q:\TEST

How would append Q:\TEST with all the rows from Q:\BLOTTER?

Suppose Q:\TEST ends at row 20.

I would like Q:\BLOTTER to be copied to Q:\TEST from row 22 onwards.

Thanks

 

[agihcam]

assuming both files are open;
try

Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr As Long
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("BLOTTER.xls")
Set sh1 = wb1.Sheets(1)
Set sh2 = wb2.Sheets(1)
Application.ScreenUpdating = False
Application.CutCopyMode = False
sh1.Range("a" & Rows.Count).End(xlUp).Offset(1).Value = "temp_value"
sh2.UsedRange.Copy
With sh1.Range("a" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial xlPasteValues
End With
With sh1.Columns("a")
Set c = .Find("temp_value", , , xlWhole)
If Not c Is Nothing Then c.ClearContents
End With
Application.CutCopyMode = True
Application.ScreenUpdating = False
End Sub