Import data macro based on X criteria

[robocop1906]

I have a spreadsheet that I’d like to import information from but to an existing tab that already has a header row. I also only want to import a certain range of data and have it based on a specific column criteria.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Path: C:\Documents and Settings<o></o>
Source File<o></o>
- File Name: Financial Workbook_Data_Store_Cleanse.xlsm<o></o>
- Tab Name: RawStampData<o></o>
- Copy Range: A2:AD<o></o>
- Criteria: Where Column AH = “Non-DEU IS”<o></o>
Destination: <o></o>
- File Name: 2011 DEU Projects - Financial Workbook.xlsm<o></o>
- Tab Name: StAmP NonCIL Data<o></o>
- Paste Range:A2:AD<o></o>

 

[Desu Nota from Columbus]

Sub MoveData()
Dim r As Long
Application.ScreenUpdating = False

Workbooks.Open Filename:= _
        "C:\Documents and Settings\2011 DEU Projects - Financial Workbook.xlsm"
With Workbooks("Financial Workbook_Data_Store_Cleanse").Sheets("RawStampData")
For r = 30000 To 2 Step -1
    If .Cells(r, 34).Value = "Non-Deu Is" Then
        .Range("A2:D2").Copy (Workbooks("2011 DEU Projects - Financial Workbook").Sheets("StAmP NonCiL Data").Range("A" & r))
End If
Next r
End With
Application.ScreenUpdating = True

End Sub

Try this and let me know how it works...and as always test this on a copy of your real data first!

 

[robocop1906]

I get the following error. on this line of code-->With Workbooks("Financial Workbook_Data_Store_Cleanse").Sheets("RawStampData")

Run-time error ‘-2147352565 (8002000b)’:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Index refers beyond end of list.<o></o>

 

[Desu Nota from Columbus]

I am not familiar with that error, but my guess is there might be a spelling error or not exact match with either the workbook or sheet name.

Make sure there are no leading or trailing spaces in the name of either wbook or wsheet.