Help on solving an error..!!!

[Hurundee]

I was writing a macro to :
1.) Open multiple files. (approx 25 files, with names having _sample_Excel.xls in common)
2.) Copy specific cells from these files.
3.) Paste special in a new file (Invoices_sample_consolidated.xls)
on a single worksheet, one below the other.

The macro written below works till point no 1 and is giving me an error beginning first line point no 2.

Can someone help me out and tell me how to solve this error?

Thanks,

Please Login or Register  to view this content.

[rylo]

Hi

Firstly, can you please remember to wrap your code in future. I've edited your post for you this time.

Is the line

Please Login or Register  to view this content.

correct, or is it a typo. There is a trailing * on the line.

Have you stepped through your program to see what is in the variable strFile when it is first initialised.

Also can you review the line starting with

Please Login or Register  to view this content.

This doesn't seem to gel with the line mentioned above.


rylo

[ShaunM]

Further to Rylo's comments, i think the line below, is wrong:

Please Login or Register  to view this content.

I think it may need to be:

Please Login or Register  to view this content.

Shaun

[Hurundee]

Thanks guys,

Well I have edited the typo errors now. Made few changes as well, I am running the script from third workbook instead of consolidated.xls. Well it works now thanks for your help.

cheers
Hurundee

Here is the revised working script:

Public Sub Sample_consolidator_1()
'

' Sample_consolidator_1 Macro

Dim strPath As String, strResultsWBName As String, strFileName As String, strFile As String
Dim i As Integer
Dim wbCurrent As Workbook
Dim tester As Workbook
strPath = "C:\My Documents\Testing" 'where the workbooks are saved
strResultsWBName = "C:\My Documents\Testing\consolidated.xls"
Set tester = Workbooks.Open(strResultsWBName)
i = 1


' 1) Open each workbook in a folder
strFile = Dir(strPath & "*_sample_Excel.xls")
While strFile <> ""
Set wbCurrent = Workbooks.Open(strPath & strFile)

' 2) Copy the relevant range of data on the 'results' sheet

wbCurrent.Worksheets("sheet1").Range("A2:I6").Copy

'3) Paste the data in the Invoices_sample_consolidated workbook
', Cells(j, 9)
tester.Worksheets("sheet1").Cells(i, 1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone

' 4) Close the _sample_Excel.xls files
wbCurrent.Close savechanges:=True

strFile = Dir
i = i + 5


Wend
tester.Close savechanges:=True
End Sub

[mudraker]

Hurundee


From rylo to you

Firstly, can you please remember to wrap your code in future. I've edited your post for you this time.

You have not wrapped your VBA code in your latest reply

Please take a couple of minutes and read the Forum Rules then wrap your VBA code (Rule 3) also make sure you read rule 7