JediYodaNT
New Member
- Joined
- Apr 20, 2007
- Messages
- 38
Hello there my fellow Excel junkies...lol.
I have been staring at some code for the past couple days and I am afraid to say, my VB knowledge is so minimal that I'm not making sense of the macros I use. I have a couple that we use for certain projects on a monthly basis. Their functionality tells me that what I need in a new macro should be possible. So, this is where I turn to the most helpful people I've ever worked with...the users for the Mr Excel forums. For years you guys have been my lifesavers.
So, let me get to the meet of my project. I manage a process each month where I take a master file, review it for values that our offcers need to look into. I then have a macro that was created before I joined the team. This macro hides columns that the officers don't need to see, and then splits the file up into individual files for each officer. From there, I send out the files to each officer, and over the next week, they would return them before their deadline. Normally this kind of trickle made this managable, however this month, we've been asked to change up the process. We uploaded the files into one document library on SharePoint for them to review and maintain with a deadline for the files to be removed and consolidated. This is where I could use some help.
The end goal is to create a macro that can open each of the 26 officer files, unhide any hidden rows and columns, remove any filters and then, copy the rows (minus the header), and paste them all onto one master file. I have a macro that I use for another process that can open a folder of files and pull over the data onto a master file, but it's too specific and I've not been able to translate it enough to reverse engineer it. Any help would be greatly apprecaited.
I installed the RDBMerge Add-In yesterday, and I believe it might work if I could run another macro beforehand that opened each of the files, unhid everything, unfroze the view, removed all filters, then saved the file as a clean and ready file. From there, I think the RDBMerge Add-In might have worked with some tweaking of the results. (All columns needed to have the formats readjusted to Currency, Short Date, etc...)
I'm open for any advice you gurus might have. I trust your years of experience in this.
In case you'd like to see the macro I was trying to reverse engineer:
I have been staring at some code for the past couple days and I am afraid to say, my VB knowledge is so minimal that I'm not making sense of the macros I use. I have a couple that we use for certain projects on a monthly basis. Their functionality tells me that what I need in a new macro should be possible. So, this is where I turn to the most helpful people I've ever worked with...the users for the Mr Excel forums. For years you guys have been my lifesavers.
So, let me get to the meet of my project. I manage a process each month where I take a master file, review it for values that our offcers need to look into. I then have a macro that was created before I joined the team. This macro hides columns that the officers don't need to see, and then splits the file up into individual files for each officer. From there, I send out the files to each officer, and over the next week, they would return them before their deadline. Normally this kind of trickle made this managable, however this month, we've been asked to change up the process. We uploaded the files into one document library on SharePoint for them to review and maintain with a deadline for the files to be removed and consolidated. This is where I could use some help.
The end goal is to create a macro that can open each of the 26 officer files, unhide any hidden rows and columns, remove any filters and then, copy the rows (minus the header), and paste them all onto one master file. I have a macro that I use for another process that can open a folder of files and pull over the data onto a master file, but it's too specific and I've not been able to translate it enough to reverse engineer it. Any help would be greatly apprecaited.
I installed the RDBMerge Add-In yesterday, and I believe it might work if I could run another macro beforehand that opened each of the files, unhid everything, unfroze the view, removed all filters, then saved the file as a clean and ready file. From there, I think the RDBMerge Add-In might have worked with some tweaking of the results. (All columns needed to have the formats readjusted to Currency, Short Date, etc...)
I'm open for any advice you gurus might have. I trust your years of experience in this.
In case you'd like to see the macro I was trying to reverse engineer:
Code:
Sub Step_1_mcr_PSR_UpdateCollectorWorksheets()
'
' Step_1_mcr_PSR_UpdateCollectorWorksheets Macro
'
Dim strDir As String
Dim strFileName As String
Dim strPath As String
Dim i As Integer
Dim x As Integer
Dim aryFileNames()
ReDim aryFileNames(1 To 100)
Dim intRows As Integer
strDir = ActiveWorkbook.Path ' current path
strFileName = "*.xlsx" 'file type to search for
strPath = strDir & "\" & strFileName 'combined dir path and name
i = 1 'counter
x = 1 'counter
aryFileNames(i) = Dir(strPath) 'sets the first member of the array to the first file in the path
Do While aryFileNames(i) <> Empty 'while the array member value is not empty
i = i + 1 'increase counter by one
If i > UBound(aryFileNames) Then 'if the counter is greater than the number of members in the array
ReDim Preserve aryFileNames(UBound(aryFileNames) + 50) 'then extend it another 50
End If
aryFileNames(i) = Dir 'sets the next member in the array to the next file name
Loop 'keep looping while array member value is not empty
i = i - 1 'remove last increase, true up counter
While i > 0
ChDir strDir 'change dir
Workbooks.Open Filename:= _
strDir & "\" & aryFileNames(x) 'open the file in the array
Sheets("My Accounts").Select
If Worksheets("My Accounts").FilterMode = True Then
Cells.Select
Selection.AutoFilter
End If
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
'Code to copy and paste data
If UCase(Range("M1").Value) = "PSR COMMENTS" And UCase(Range("B1").Value) = "ACCOUNT NUMBER" Then
Range("B1048576").Select 'select last row
Selection.End(xlUp).Select 'find the last record
intRows = Selection.Row 'set the row number for that last record
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = Left(Right(aryFileNames(x), 9), 4)
Rows("2:" & intRows).Select 'select the rows to be copied
Selection.Copy 'copy
Windows("PSR_ConsolidateWorksheet_Processor.xlsm").Activate 'switch files
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'paste
Application.CutCopyMode = False 'clear clipboard
ActiveWorkbook.Save
Windows(aryFileNames(x)).Activate 'switch files
ActiveWindow.Close saveChanges:=False 'exit file
Windows("PSR_ConsolidateWorksheet_Processor.xlsm").Activate 'switch files
Range("B1048576").Select 'select last row
Selection.End(xlUp).Select 'find the last record
intRows = Selection.Row 'set the row number for that last record
Cells(intRows + 1, 1).Select 'go to first column of the next or new record
Sheets("WMS Check").Select
Cells(x, 1) = aryFileNames(x)
Sheets("Sheet1").Select
Else:
Application.CutCopyMode = False 'clear clipboard
ActiveWindow.Close saveChanges:=False 'exit file
Windows("PSR_ConsolidateWorksheet_Processor.xlsm").Activate 'switch files
Sheets("WMS Check").Select
Cells(x, 2) = aryFileNames(x)
Sheets("Sheet1").Select
End If
x = x + 1 'increase array counter
i = i - 1 'decrease while counter
Wend
Range("A1").Select
MsgBox "Consolidation is complete."
End Sub
Last edited:
