vba to run a macro on multiple excel files in a folder

[kevvukeka]

Hi All,

I have develop a macro which I need to run on multiple excel files in a folder. Each file in the folder contains tow sheets "Provider_Report" and "Rdata".

I have created a new excel file in the same folder and named it as test.This file will only have "sheet1,sheet2 and sheet3"

in the "test" file, I have placed below macro(got it from google, thanks to creator of it)




Sub LoopThroughFiles()
Dim myfile As String, sep As String
Dim wb1 As Workbook
Dim wsd As Worksheet, sh As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable, PT1 As PivotTable
Dim PF As PivotField
Dim prange As Range, erng1 As Range, cel2 As Range, efnd1 As Range, npefnd2 As Range, pfind3 As Range, cfind4 As Range
Dim finalcol As Long, ercount As Long, i As Long, j As Long, x As Long, y As Long, ircount As Long
Dim finalrow As Long
Dim S As String, t As String
Dim fnameandpath As Variant
Dim excwb As Workbook
Dim counter As Double





sep = Application.PathSeparator
If sep = "\" Then
myfile = Dir(CurDir() & sep & "*.xlsx")
End If

Do While myfile <> ""
Set wb1 = Workbooks.Open(Filename:=myfile)
Call crpivot

myfile = Dir
Loop

End Sub








"crpivot" is the actual macro which I need to run on this file.

But every time I run the above code, it shows me an error, since its not running on the "myfile" that is opened. its just working on "test" excel file.


I am attaching the crpivot code also, just in case if needed.

There would be around 14 excel files on which I need to run this macro. Kindly help.

Thanks in advance...I couldn't find the vba code tags to wrap my code around it.

Kindly suggest.

[Kenneth Hobs]

Just use code tags for vba code.

You need to write your macro to work on the activeworkbook or pass the workbook name or object to your routine if you must set a workbook.

e.g.

crpivot wb1


Sub crpivot (aWB as Workbook)

[kevvukeka]

Hi Kenneth,

Thanks for the reply.. changed the lines in my above code

crpivot wb1


and placed main macro in module 2

changed it to:
Sub crpivot(aWB As Workbook)


But it still doesn't work..

I am not a complete VBA guy still, so things are not completely clear to me... kindly suggest

[kevvukeka]

Hi Kenneth,

can you kindly reply on my above post.. I am still struck there....

[Kenneth Hobs]

Your docx code is very hard to read. It is not structured so I can not see what all is going on. You can copy and paste between code tags as you did for the first part if you like. It is very involved so I can not test it well.

When I answer questions, I try to teach concepts. In a like manner, if you state your questions simply and only post short examples that show the problem, it is easier to help.

For your scenario, you need to use aWB as the prefix for working with workbook rather than relying on default assumptions of an activework being what you think it is.

e.g.

Sub Test
Dim wb1 as Workbook
Set wb1 = ThisWorkbook
crpivot wb1
End Sub

Sub crpivot (aWB as Workbook)
MsgBox aWb.Worksheets(1).Range("A1").Value, , aWB.Name
End Sub


Try using Debug.Print to put parts into the Immediate window or just step through code with F8 one line at a time. I think that you will see that your assumptions that an activeworkbook is the one you thought, is wrong. That is why I gave you the short example so that you can use it more directly without making assumptions.

If you can make a short example with just that problem, you can get help to solve the problem quickly. Otherwise, post the code but keep in mind, we do not have your workbook(s) so it is hard for us to test if it is much involved.

[kevvukeka]

Hi Kenneth,

I did worked now..

crpivot wb

sub crpivot(wb as workbook)

Thanks for all the help... I am currently working on your reply for my other post related to this. .passing two workbooks into one module...

Will reply to it once I am done..

This really helped...