Results 1 to 7 of 7

Thread: edit macro to make it run on all files in a folder

  1. #1

    edit macro to make it run on all files in a folder



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Code:
    Sub extn()
    '
    ' extn Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveWindow.ScrollWorkbookTabs Sheets:=-24
    ActiveWindow.ScrollWorkbookTabs Sheets:=-10
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Jul1"
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Jul2"
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Jul3"
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "Jul4"
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "Jul5"
    Sheets("Sheet6").Select
    Sheets("Sheet6").Name = "Jul6"
    Sheets("Sheet7").Select
    Sheets("Sheet7").Name = "Jul7"
    Sheets("Sheet8").Select
    Sheets("Sheet8").Name = "Jul8"
    Sheets("Sheet9").Select
    Sheets("Sheet9").Name = "Aug1"
    Sheets("Sheet10").Select
    Sheets("Sheet10").Name = "Aug2"
    Sheets("Sheet11").Select
    Sheets("Sheet11").Name = "Aug3"
    Sheets("Sheet12").Select
    Sheets("Sheet12").Name = "Aug4"
    Sheets("Sheet13").Select
    Sheets("Sheet13").Name = "Aug5"
    Sheets("Sheet14").Select
    Sheets("Sheet14").Name = "Aug6"
    Sheets("Sheet15").Select
    Sheets("Sheet15").Name = "Aug7"
    Sheets("Sheet16").Select
    Sheets("Sheet16").Name = "Aug8"
    Sheets("Sheet17").Select
    Sheets("Sheet17").Name = "Sep1"
    Sheets("Sheet18").Select
    Sheets("Sheet18").Name = "Sep2"
    Sheets("Sheet19").Select
    Sheets("Sheet19").Name = "Sep3"
    Sheets("Sheet20").Select
    Sheets("Sheet20").Name = "Sep4"
    Sheets("Sheet21").Select
    Sheets("Sheet21").Name = "Sep5"
    Sheets("Sheet22").Select
    Sheets("Sheet22").Name = "Sep6"
    Sheets("Sheet23").Select
    Sheets("Sheet23").Name = "Sep7"
    Sheets("Sheet24").Select
    Sheets("Sheet24").Name = "Sep8"
    Sheets("Sheet25").Select
    Sheets("Sheet25").Name = "Oct1"
    Sheets("Sheet27").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=7
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Sheet26").Select
    Sheets("Sheet26").Name = "Oct2"
    Sheets("Sheet27").Select
    Sheets("Sheet27").Name = "Oct3"
    Sheets("Sheet28").Select
    Sheets("Sheet28").Name = "Oct4"
    Sheets("Sheet29").Select
    Sheets("Sheet29").Name = "Oct5"
    Sheets("Sheet30").Select
    Sheets("Sheet30").Name = "Oct6"
    Sheets("Sheet31").Select
    Sheets("Sheet31").Name = "Oct7"
    Sheets("Sheet32").Select
    Sheets("Sheet32").Name = "Oct8"
    Sheets("Sheet33").Select
    Sheets("Sheet33").Name = "Nov1"
    Sheets("Sheet34").Select
    Sheets("Sheet34").Name = "Nov2"
    Sheets("Sheet35").Select
    Sheets("Sheet35").Name = "Nov3"
    Sheets("Sheet36").Select
    Sheets("Sheet36").Name = "Nov4"
    Sheets("Sheet37").Select
    Sheets("Sheet37").Name = "Nov5"
    Sheets("Sheet38").Select
    Sheets("Sheet38").Name = "Nov6"
    Sheets("Sheet39").Select
    Sheets("Sheet39").Name = "Nov7"
    Sheets("Sheet40").Select
    Sheets("Sheet40").Name = "Nov8"
    ActiveWindow.ScrollWorkbookTabs Sheets:=10
    Sheets("Sheet41").Select
    Sheets("Sheet41").Name = "Dec1"
    Sheets("Sheet42").Select
    Sheets("Sheet42").Name = "Dec2"
    Sheets("Sheet43").Select
    Sheets("Sheet43").Name = "Dec3"
    Sheets("Sheet44").Select
    Sheets("Sheet44").Name = "Dec4"
    Sheets("Sheet45").Select
    Sheets("Sheet45").Name = "Dec5"
    Sheets("Sheet46").Select
    Sheets("Sheet46").Name = "Dec6"
    Sheets("Sheet47").Select
    Sheets("Sheet47").Name = "Dec7"
    Sheets("Sheet48").Select
    Sheets("Sheet48").Name = "Dec8"
    Range("P22").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=-25
    ActiveWindow.ScrollWorkbookTabs Sheets:=-13
    Sheets("Jun8").Select
    Cells.Select
    Selection.Copy
    Sheets("Jul1").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets(Array("Jul1", "Jul2", "Jul3", "Jul4", "Jul5", "Jul6", "Jul7", "Jul8", "Aug1", _
    "Aug2", "Aug3", "Aug4", "Aug5", "Aug6", "Aug7", "Aug8", "Sep1", "Sep2", "Sep3", "Sep4", _
    "Sep5", "Sep6", "Sep7", "Sep8", "Oct1")).Select
    Sheets("Jul1").Activate
    Sheets(Array("Oct2", "Oct3", "Oct4", "Oct5", "Oct6", "Oct7", "Oct8", "Nov1", "Nov2", _
    "Nov3", "Nov4", "Nov5", "Nov6", "Nov7", "Nov8", "Dec1", "Dec2", "Dec3", "Dec4", "Dec5", _
    "Dec6", "Dec7", "Dec8")).Select Replace:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Range("B6:H13").Select
    Application.CutCopyMode = False
    Range("B5").Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    Range("G4:H4").Select
    ActiveWindow.LargeScroll Down:=1
    Range("B30:H40").Select
    ActiveWindow.LargeScroll Down:=1
    Range("A58:E58").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("A30:A40").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("A3").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Dec8").Select
    Range("B16:H28").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=-23
    Sheets("Jun7").Select
    End Sub

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Do you think we can guess what you mean?

    Do you really want to run that inefficient code on all workbooks in a folder? For example it moves through twelve sheets for no apparent reason.
    Hope that helps

    Roy

  3. #3
    Hi! RoyUK

    Do you think you have understood what I am looking for. Don't you think you should not have replied if you don't know how to work with macros at all. Don't you think this is a very basic thing that I am looking for and you should stop visiting this site as you don't understand even the basics.

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    I bow to your superior knowledge & coding skills.
    Hope that helps

    Roy

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    rahul

    No one understands what you're looking for.

    You don't indicate where you are starting from.
    You don't indicate what you are trying to do.
    The road map you posted to get from one to the other obviously doesn't work or you wouldn't be here.

    How about using words to describe your situation and perhaps some one will then be able to assist.


    PS: type " Roy Cox Excel " into Google and see what comes up.

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    "I bow to your superior knowledge & coding skills." Roy, that is pure gold! Well said.

    Rahul...it's certainly possible for someone to read through your code, and try to guess what you are trying to do. But that takes time. And this forum is manned by volunteers, who give up their own time to help people like you. So if you want help with something, you should include a description of what you are trying to achieve. And you should be polite. Otherwise, noone will give up their time to help you.

    I suggest you apologize to Roy for you comment - which was completely wrong because Roy certainly does know VBA - and post a brief description of what you are trying to do, and then someone will take a look and help you out.

  7. #7
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    31
    Articles
    0
    Excel Version
    2016 64bit
    Cross-post: w ww.excelforum.com/excel-programming-vba-macros/929058-edit-macro-to-make-it-run-on-all-files-in-a-folder.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •