Copy range from Open Workbook without naming file path

[oplintx]

I am trying to figure out how to copy a range of data from a open file WITHOUT naming a file path. The file path varies, and is a temp file downloaded from our system. I would only like to apply this macro only when a file called pricedownload.csv is open

My work team each downloads a pricing file each day and I have another file that applies a margin by customer. We download a file column A2:J and paste it into the work

1.) Check to see if "pricedownload.csv" is open
A. If No, Error "please open pricedownload file first"
B. If Yes, Step 2
2.) Find Range in "pricedownload.csv" file, using the active sheet starting in A2 and end in column J (rows vary)
3.) Select and then Copy
4.) Go to destination workbook "Rack Pricing" and paste values in A3.

I am new to VBA, I know logically how it should work but am clueless on the code.

 

[VBA Geek]

Sub cp()
Dim objPriceFile As Workbook
On Error Resume Next
    Set objPriceFile = Workbooks("pricedownload.csv")
On Error GoTo 0
If objPriceFile Is Nothing Then MsgBox "Please Open Price File", vbExclamation: Exit Sub
    With objPriceFile.Sheets(1)
        .Range("A2:J" & .Range("J" & Rows.Count).End(xlUp).Row).Copy Workbooks("Rack Pricing.xlsm").Sheets("Sheet1").Range("A3")
    End With
End Sub

 

[oplintx]

That works awesome. Thank you!!!

 

[oplintx]

One more thing...

Some times the file downloaded is

pricedownload (1).csv instead of pricedownload.csv. I don't know why this happens ​

cuz their are not multiple instances open.

Can I say change this to Set objPriceFile like Workbooks("pricedownload(.*).csv")

Thanks​

 

[VBA Geek]

you cannot use wildcards if that is how you intended to use the "*"

you can do this:

Sub prcDown()
Dim objPriceFile As Excel.Workbook
On Error Resume Next
Set objPriceFile = Workbooks("pricedownload.csv")

If objPriceFile Is Nothing Then
    Set objPriceFile = Workbooks("pricedownload (1).csv")
End If
On Error GoTo 0
End Sub

 

[oplintx]

How would this code be rewritten to capture files that do change. I have a file that each time is downloaded, it is renamed....ie...Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6,Sheet7,Sheet8,ect.

I am not sure how to make this code work for something like that.

thanks