Copy & Paste

J

Joe

Hello everyone,

I need some help on the following:

1)from excel file A, I want have a combo box showing the
xls files in a given directory
2) select a file X and open it
3) select any row containing data=xxx in the column AA of
the file X
4)deleting such rows
5)copy all column from A to AX
6) closing the file X without saving the changes
7) selecting the cell A9 in the sheet"Data" of the file A
8)paste the previously copied data

Tht's all folks!

Thanks in advance for any suggestion
Regards
 
D

Dave Peterson

This seemed to work ok for me. I opened book4.xls (you'll use your workbook
from the combobox). And I used sheet1 from that workbook.

I removed any autofilter arrows. Inserted a new row 1, applied autofilter to
that AA column. filtered on my string to delete, deleted the visible cells and
copied into the data worksheet. (and closed that workbook).

Option Explicit
Sub testme01()

Dim dataWks As Worksheet
Dim fromWks As Worksheet
Dim strToDelete As String
Dim dummyRng As Range

'Workbooks.Open Filename:="book4.xls"
'assumes you already opened the From Workbook.
Set fromWks = Workbooks("book4.xls").Worksheets("sheet1")

Set dataWks = ThisWorkbook.Worksheets("Data")
strToDelete = "asdf"

With fromWks
.Rows(1).Insert
.Range("aa1").Value = "Temp"
.AutoFilterMode = False
.Range("aa:aa").AutoFilter field:=1, Criteria1:=strToDelete
'just delete the visible cells--include the temporary header
.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible) _
.EntireRow.Delete
Set dummyRng = .UsedRange 'try to reset lastused cell
.Range("A1", _
.Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row, "AX")).Copy _
Destination:=dataWks.Range("a9")
.Parent.Close savechanges:=False
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top