kevvukeka
08-08-2013, 06:36 AM
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.
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.