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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

S
  • Question Question
Change macro range from column A to B
2
Replies
10
Views
146
sachin483
S
4
  • Question Question
VBA to Print Labels but Issues Selecting Printer
Replies
1
Views
314
rpaulson
rpaulson
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
I
  • Solved
Simplify my working code please
Replies
7
Views
186
ipbr21054
I
S
  • Question Question
Conditional Formatting changes from XLSM to XLSX when I use VBA code to save a new file
Replies
1
Views
481
JerrExcel
JerrExcel

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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