PDA

View Full Version : Converting a range to a table and then creating a pivot table through a series of WBs



Tanner_2004
11-25-2015, 01:00 PM
Hi All:

I have tried to solve this on my old without much success, I did get as far as this. However, I am looking to do this:

I have this code that loops through all of the workbooks in a folder (as you can see). I then want to change the range that starts in Sheet1, cell A1, to a Table.
From that point, I want to create a basic pivot table from the Table that was just created, and inset the pivot table on a new worksheet. Once this is procedure is run, I want to close the workbook, save the changes and do the sane for the next workbook in the designated folder. The range will always be in Sheet1 and start in A1, however, the range will change in width (columns) and length/height (rows).


Sub Rename_Sheet1()
Dim strPathh As String, strFile As String, counter As Long

strPath = "C:\Users\lru8079\Desktop\test\"
strFile = Dir(strPath & "*.xlsx")

Application.ScreenUpdating = False
Do While strFile <> ""
With Workbooks.Open(strPath & strFile)
.Sheets(1).Name = "Sheet1"
.Close SaveChanges:=True
End With
counter = counter + 1
strFile = Dir
Loop
Application.ScreenUpdating = True

MsgBox counter & " files altered. ", vbInformation, "Process Complete"

End Sub

Thank you for reading and any advice.