How can I simplify this one?

ryan0521

ryan0521

Board Regular
Joined
Dec 7, 2016
Messages
79
Code: 
'one
    If mwkb.Sheets("Cover Sheet").Range("ab11").Value = 1 Then
        Set Wkb = Workbooks.Open(PATH1)
        mwkb.Sheets("1").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
    End If
    
'two
    If mwkb.Sheets("Cover Sheet").Range("ab11").Value = 2 Then
        Set Wkb = Workbooks.Open(PATH1)
        mwkb.Sheets("1").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH2)
        mwkb.Sheets("2").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("2").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
    End If
    
'three
    If mwkb.Sheets("Cover Sheet").Range("ab11").Value = 3 Then
        Set Wkb = Workbooks.Open(PATH1)
        mwkb.Sheets("1").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH2)
        mwkb.Sheets("2").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("2").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH3)
        mwkb.Sheets("3").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("3").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
    End If
    
'four
    If mwkb.Sheets("Cover Sheet").Range("ab11").Value = 4 Then
        Set Wkb = Workbooks.Open(PATH1)
        mwkb.Sheets("1").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH2)
        mwkb.Sheets("2").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("2").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH3)
        mwkb.Sheets("3").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("3").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH4)
        mwkb.Sheets("4").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("4").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
    End If
    
'five
    If mwkb.Sheets("Cover Sheet").Range("ab11").Value = 5 Then
        Set Wkb = Workbooks.Open(PATH1)
        mwkb.Sheets("1").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH2)
        mwkb.Sheets("2").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("2").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH3)
        mwkb.Sheets("3").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("3").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH4)
        mwkb.Sheets("4").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("4").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH5)
        mwkb.Sheets("5").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("5").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
    End If
    
'six
    If mwkb.Sheets("Cover Sheet").Range("ab11").Value = 6 Then
        Set Wkb = Workbooks.Open(PATH1)
        mwkb.Sheets("1").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH2)
        mwkb.Sheets("2").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("2").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH3)
        mwkb.Sheets("3").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("3").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH4)
        mwkb.Sheets("4").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("4").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH5)
        mwkb.Sheets("5").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("5").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
        Set Wkb = Workbooks.Open(PATH6)
        mwkb.Sheets("6").Cells.ClearContents
        Wkb.ActiveSheet.Cells.Copy
        mwkb.Activate
        Sheets("6").Select
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1:N130").Select
        With Selection
            .NumberFormat = "General"
            .Value = .Value
        End With
        Range("A1").Select
        Wkb.Close savechanges:=False
        Cnt = Cnt + 1
        r = r + 1
    End If
 
Last edited by a moderator:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Please use code tags around your code - click on # icon above post window and paste your code between the code tags

Here is the first one - now it your turn to attack the others
- the main thing to realise is that you do not have to select everything in sight
- create your variables and refer to sheets and ranges without selecting them to copy, paste, format etc

When using multiple workbooks and worksheets it is better practice to qualify all ranges
-Range("A1:N130") is not qualified but it belongs inside a workheet and workbook
Is it the first sheet or is the sheet named "1" that is being referred to with Sheets("1")
- if you want to refer to the first sheet regardless of its name, then use Sheets(1) which is the index number of the sheet (ie its positon in the workbook)

Code: 
If mwkb.Sheets("Cover Sheet").Range("ab11").Value = 1 Then
    Set Wkb = Workbooks.Open(PATH1)
    mwkb.Sheets("1").Cells.ClearContents
    Wkb.ActiveSheet.Cells.Copy  mwkb.Sheets("1").Range("A1")
    Application.CutCopyMode = False
    With Range("A1:N130")
        .NumberFormat = "General"
        .Value = .Value
    End With
    Range("A1").Select
    Wkb.Close savechanges:=False
    Cnt = Cnt + 1
    r = r + 1
End If
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Scraping a table from a website, VBA code needs to be updated, running into issues
Replies
1
Views
344
Dan_W
D
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
133
willow1985
willow1985
B
  • Solved
VBA is there a way to speed up?
Replies
4
Views
232
RoryA
RoryA
F
  • Question
VBA Import columns by headers - Need prompts to select workbook/sheet name
Replies
2
Views
211
floggingmolly
F
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
220
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,477
Messages
6,125,038
Members
449,205
Latest member
Eggy66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top