Results 1 to 8 of 8

Loop to copy from multiple files and sheets to one long list

Threaded View

  1. #1
    Registered User
    Join Date
    03-03-2012
    Location
    water valley, ms
    MS-Off Ver
    Excel 2007
    Posts
    4

    Talking Loop to copy from multiple files and sheets to one long list

    Hi everyone. I am updating a macro that looks like this:
    Private Sub CheckBox1_Click()
        Windows("Daily Shift.xls").Activate
        Sheets("1").Select
        Range("A4:P43").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Weekly.xls").Activate
        Sheets("Summary").Select
        Range("K2").Select
        ActiveSheet.Paste
        
        Windows("Daily Shift.xls").Activate
        Sheets("1").Select
        Range("R4:AG43").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Weekly.xls").Activate
        Sheets("Summary").Select
        Range("K42").Select
        ActiveSheet.Paste
     
    Windows("2nd Shift.xls").Activate
        Sheets("1").Select
        Range("A4:P43").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Weekly.xls").Activate
        Sheets("Summary").Select
        Range("K82").Select
        ActiveSheet.Paste
        
        Windows("2nd Shift.xls").Activate
        Sheets("1").Select
        Range("R4:AG43").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Weekly.xls").Activate
        Sheets("Summary").Select
        Range("K122").Select
        ActiveSheet.Paste
        
     Windows("3rd Shift.xls").Activate
        Sheets("1").Select
        Range("A4:P43").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Weekly.xls").Activate
        Sheets("Summary").Select
        Range("K162").Select
        ActiveSheet.Paste
        
        Windows("3rd Shift.xls").Activate
        Sheets("1").Select
        Range("R4:AG43").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Weekly.xls").Activate
        Sheets("Summary").Select
        Range("K202").Select
        ActiveSheet.Paste
     
    End Sub
    
    Private Sub CheckBox10_Click()
        
    Windows("Daily Shift.xls").Activate
        Sheets("10").Select
        Range("A4:P43").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Weekly.xls").Activate
        Sheets("Summary").Select
        Range("K2162").Select
        ActiveSheet.Paste
    .....and so one for quite a while. My revision will triple the amount of codes. I am looking to write any kind of loop that will reduce the amount of code.
    The can be a loop for each window, or anything will help
    Last edited by sdb21; 03-04-2012 at 08:51 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1