Opening and Copying Sheets to Certain Sheets within Master Workbook

Question

I have two workbooks: Master and TMT.

I want to open TMT and paste:

Sheet(1) to the "TMT1" sheet in the Master workbook
Sheet(2) to the "TMT2" sheet in the Master workbook
Sheet(4) to the "TMT3" sheet in the Master workbook
Sheet(5) to the "TMT4" sheet in the Master workbook

I have provided some long winded code which does this, however I would love to be able to loop it to make the code more efficient!

Here is the code I have so far!

Sub TMT()
    Dim wbMaster As Workbook, wbCons As Workbook, wsMaster As Worksheet

    Application.ScreenUpdating = False
    Set wbMaster = ThisWorkbook
    Set wbTMT = Workbooks.Open("/Users/edwardlee/Downloads/TMT.xlsm")

    wbTMT.Sheets(1).Cells.Copy
    wbMaster.Sheets("TMT1").Activate
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste

    wbTMT.Sheets(2).Cells.Copy
    wbMaster.Sheets("TMT2").Activate
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste

    wbTMT.Sheets(4).Cells.Copy
    wbMaster.Sheets("TMT3").Activate
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste

    wbTMT.Sheets(5).Cells.Copy
    wbMaster.Sheets("TMT4").Activate
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste

End Sub

Mikku · Answer 1 · 2019-07-28T06:51:14.757

I don't think Loop will be Suitable, as you have different names for 4Th sheet. Anyway you will need to put conditions for them, and that will drag your code lengthier than this. Loops would have been an option if your sheets to copy were in line but they are not (1,2,4,5)

But you can optimize the code by NOT Using Select & Activate

Simplified:

Sub TMT()

    Dim wbMaster As Workbook, wbCons As Workbook, wsMaster As Worksheet

    Application.ScreenUpdating = False
    Set wbMaster = ThisWorkbook
    Set wbTMT = Workbooks.Open("/Users/edwardlee/Downloads/TMT.xlsm")

    wbTMT.Sheets(1).Cells.Copy wbMaster.Sheets("TMT1").Range("A1")
    wbTMT.Sheets(2).Cells.Copy wbMaster.Sheets("TMT2").Range("A1")
    wbTMT.Sheets(4).Cells.Copy wbMaster.Sheets("TMT3").Range("A1")
    wbTMT.Sheets(5).Cells.Copy wbMaster.Sheets("TMT4").Range("A1")

    Application.ScreenUpdating = True

End Sub

Also you were missing the Application.Screenupdating at the End of code

Loop would be something Like this: this will paste data from 5 sheets in wbTMT to wbMaster. Run it to 4 if the numbers are in Sync like (1,2,3,4)

For  i = 1 to 5 

wbTMT.Sheets(i).Cells.Copy wbMaster.Sheets("TMT" & i).Range("A1")

Next

To check the Sheet Number you can use Property Sheet.Index

For each sht in wbTMT.WorkSheets

    If sht.Index = 3 then 
        'Blah Blah Blah
    End if 

Next

Hi, thanks for quick reply: 1) Can I use an if statement in the loop to see if sheet = sheet(3)? 2) How would that loop look? 3) I am able to reformat the TMT sheet so that I can take the first four sheets, thus it would be linear. — Ed Lee, Jul 28 '19 at 06:40

Opening and Copying Sheets to Certain Sheets within Master Workbook

1 Answers1