I have a macro that runs well to update a workbook every month. There are
25 workbooks, so we need to open every one and run the macro. I would like
to have a macro that would open them one-by-one and nun my macro. My macro
could be embedded or launched by this new macro. I know the various
processes for looping (For each, Do Until, etc) but I do not know how to
use these with a group of workbooks in a folder. Can someone please help?
My macro could be embedded or launched by this new macro. I know the
various processes for looping (For each, DoUntil, etc) but I do not know
how to use these with a group of workbooks in a folder. Can someone please
help? Here is the code I need to run for each workbook:
'PROCESS PROCEDURES FOR EACH WORKBOOK
'REFRESH LINKS TO DATABASE
Sheets("variance data").Select
Range("J50").Select
Selection.ListObject.QueryTable.refresh BackgroundQuery:=False
Sheets("cy act & fcast trend ").Select
Range("J1613").Select
Selection.ListObject.QueryTable.refresh BackgroundQuery:=False
Sheets("Curr Full YR Fcst").Select
Range("J50").Select
Selection.ListObject.QueryTable.refresh BackgroundQuery:=False
ActiveWorkbook.Save
'
'SAVE WORKBOOK TO AN ARCHIVE FOLDER
Dim ReportingMonth As String
ReportingMonth = "Sept 2012" '
'
ChDir _
"C:\Documents and Settings\B612278\My Documents\Reporting
Database\Macro Test Area\GO\ArchiveCopy"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\B612278\My Documents\Reporting
Database\Macro Test Area\GO\ArchiveCopy\All HR Reporting1 " &
ReportingMonth & ".xlsm"
'TURN REPORTING PAGES INTO ALL VALUE
Dim WS As Worksheet
Set WS = Worksheets(1)
Do Until WS.Name = "variance data"
With WS.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
'.Cells(1).Select
End With
Application.CutCopyMode = False ' do something with WS
Set WS = WS.Next
Loop
'DELETE DATA SHEETS
Sheets("variance data").Select
ActiveWindow.SelectedSheets.Delete
Sheets("cy act & fcast trend ").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Curr Full YR Fcst").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Curr yr budget & Prev Yr Trend ").Select
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Save
ActiveWorkbook.Close
ChDir "C:\Documents and Settings\B612278\My Documents\Reporting
Database\Macro Test Area\GO
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
