help streamlining VBA

B

BiKeTa

New Member
Joined
Mar 31, 2018
Messages
15
I have a project I am creating for work but i am unable to do it as my VBA keeps saying code too long. let me try to explain first what i need. there are a total of 5 sheets of data. i want to be able to copy paste a particular area of info from one place to another via entering data(in this case a room number) into a cell and using a button(hyperlink style) to paste the area based on the room entered in the cell. there are roughly 30 room per sheet and they need to moved between any of the sheets
below is what i have but it is only for one room that can be transferred to any other location but i need to do this for what ends up being about 140 rooms or 30-34 rooms per sheet

i am kinda of a novice but i am learning what i can from online forums but this has gotten to advanced to find what i am looking for
Please help me

Code: 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'Check if the Target Address is same as you have given


 For i = 1 To Sheets.Count
 Sheets(i).UnProtect
 Next i
     
  
If MsgBox("This will move all data of current room to new selected room! Are you sure you want to continue?", vbYesNo) = vbNo Then Exit Sub




  If Target.Range.Address = "$F$10" Then
        'Write your all VBA Code, which you want to execute
        'Or Call the function or Macro which you have
        'written or recorded.
If Sheet9.Range("F9") <> "201" Then
If Sheet9.Range("F9") <> "202-1" Then
If Sheet9.Range("F9") <> "202-2" Then
If Sheet9.Range("F9") <> "203" Then
If Sheet9.Range("F9") <> "204" Then
If Sheet9.Range("F9") <> "205" Then
If Sheet9.Range("F9") <> "206" Then
If Sheet9.Range("F9") <> "207" Then
If Sheet9.Range("F9") <> "208" Then
If Sheet9.Range("F9") <> "209" Then
If Sheet9.Range("F9") <> "210" Then
If Sheet9.Range("F9") <> "211" Then
If Sheet9.Range("F9") <> "212-1" Then
If Sheet9.Range("F9") <> "212-2" Then
If Sheet9.Range("F9") <> "213" Then
If Sheet9.Range("F9") <> "214-1" Then
If Sheet9.Range("F9") <> "214-2" Then
If Sheet9.Range("F9") <> "215" Then
If Sheet9.Range("F9") <> "216-1" Then
If Sheet9.Range("F9") <> "216-2" Then
If Sheet9.Range("F9") <> "217" Then
If Sheet9.Range("F9") <> "218-1" Then
If Sheet9.Range("F9") <> "218-2" Then
If Sheet9.Range("F9") <> "219" Then
If Sheet9.Range("F9") <> "220-1" Then
If Sheet9.Range("F9") <> "220-2" Then
If Sheet9.Range("F9") <> "221" Then
If Sheet9.Range("F9") <> "222-1" Then
If Sheet9.Range("F9") <> "222-2" Then
If Sheet9.Range("F9") <> "223" Then
If Sheet9.Range("F9") <> "224" Then
If Sheet9.Range("F9") <> "225-1" Then
If Sheet9.Range("F9") <> "225-2" Then
If Sheet9.Range("F9") <> "226" Then
If Sheet9.Range("F9") <> "227-1" Then
If Sheet9.Range("F9") <> "227-2" Then
If Sheet9.Range("F9") <> "228" Then
If Sheet9.Range("F9") <> "229-1" Then
If Sheet9.Range("F9") <> "229-2" Then
If Sheet9.Range("F9") <> "230" Then
If Sheet9.Range("F9") <> "232" Then
If Sheet9.Range("F9") <> "234" Then
If Sheet9.Range("F9") <> "236" Then
If Sheet9.Range("F9") <> "237" Then
If Sheet9.Range("F9") <> "238" Then
If Sheet9.Range("F9") <> "239" Then
If Sheet9.Range("F9") <> "240" Then
If Sheet9.Range("F9") <> "241" Then
If Sheet9.Range("F9") <> "242" Then
If Sheet9.Range("F9") <> "243" Then
If Sheet9.Range("F9") <> "301" Then
If Sheet9.Range("F9") <> "302-1" Then
If Sheet9.Range("F9") <> "302-2" Then
If Sheet9.Range("F9") <> "303" Then
If Sheet9.Range("F9") <> "304" Then
If Sheet9.Range("F9") <> "305" Then
If Sheet9.Range("F9") <> "306" Then
If Sheet9.Range("F9") <> "307" Then
If Sheet9.Range("F9") <> "308" Then
If Sheet9.Range("F9") <> "309" Then
If Sheet9.Range("F9") <> "310" Then
If Sheet9.Range("F9") <> "311" Then
If Sheet9.Range("F9") <> "312-1" Then
If Sheet9.Range("F9") <> "312-2" Then
If Sheet9.Range("F9") <> "313" Then
If Sheet9.Range("F9") <> "314-1" Then
If Sheet9.Range("F9") <> "314-2" Then
If Sheet9.Range("F9") <> "315" Then
If Sheet9.Range("F9") <> "316-1" Then
If Sheet9.Range("F9") <> "316-2" Then
If Sheet9.Range("F9") <> "317" Then
If Sheet9.Range("F9") <> "318-1" Then
If Sheet9.Range("F9") <> "318-2" Then
If Sheet9.Range("F9") <> "319" Then
If Sheet9.Range("F9") <> "320-1" Then
If Sheet9.Range("F9") <> "320-2" Then
If Sheet9.Range("F9") <> "321" Then
If Sheet9.Range("F9") <> "322-1" Then
If Sheet9.Range("F9") <> "322-2" Then
If Sheet9.Range("F9") <> "323" Then
If Sheet9.Range("F9") <> "424" Then
If Sheet9.Range("F9") <> "425-1" Then
If Sheet9.Range("F9") <> "425-2" Then
If Sheet9.Range("F9") <> "426" Then
If Sheet9.Range("F9") <> "427-1" Then
If Sheet9.Range("F9") <> "427-2" Then
If Sheet9.Range("F9") <> "428" Then
If Sheet9.Range("F9") <> "429-1" Then
If Sheet9.Range("F9") <> "429-2" Then
If Sheet9.Range("F9") <> "430" Then
If Sheet9.Range("F9") <> "431-1" Then
If Sheet9.Range("F9") <> "431-2" Then
If Sheet9.Range("F9") <> "432" Then
If Sheet9.Range("F9") <> "434" Then
If Sheet9.Range("F9") <> "435-1" Then
If Sheet9.Range("F9") <> "435-2" Then
If Sheet9.Range("F9") <> "436" Then
If Sheet9.Range("F9") <> "437" Then
If Sheet9.Range("F9") <> "438" Then
If Sheet9.Range("F9") <> "439" Then
If Sheet9.Range("F9") <> "440" Then
If Sheet9.Range("F9") <> "441" Then
If Sheet9.Range("F9") <> "442" Then
If Sheet9.Range("F9") <> "443" Then
If Sheet9.Range("F9") <> "444" Then
If Sheet9.Range("F9") <> "445-1" Then
If Sheet9.Range("F9") <> "445-2" Then
If Sheet9.Range("F9") <> "361" Then
If Sheet9.Range("F9") <> "362" Then
If Sheet9.Range("F9") <> "363" Then
If Sheet9.Range("F9") <> "364" Then
If Sheet9.Range("F9") <> "365" Then
If Sheet9.Range("F9") <> "366" Then
If Sheet9.Range("F9") <> "367" Then
If Sheet9.Range("F9") <> "368" Then
If Sheet9.Range("F9") <> "369" Then
If Sheet9.Range("F9") <> "370" Then
If Sheet9.Range("F9") <> "371" Then
If Sheet9.Range("F9") <> "372" Then
If Sheet9.Range("F9") <> "373" Then
If Sheet9.Range("F9") <> "374" Then
If Sheet9.Range("F9") <> "375" Then
If Sheet9.Range("F9") <> "376" Then
If Sheet9.Range("F9") <> "377" Then
If Sheet9.Range("F9") <> "378" Then
If Sheet9.Range("F9") <> "379" Then
If Sheet9.Range("F9") <> "380" Then
If Sheet9.Range("F9") <> "381" Then
If Sheet9.Range("F9") <> "382" Then
If Sheet9.Range("F9") <> "383" Then
If Sheet9.Range("F9") <> "384" Then
If Sheet9.Range("F9") <> "385" Then
If Sheet9.Range("F9") <> "386" Then
If Sheet9.Range("F9") <> "387" Then
If Sheet9.Range("F9") <> "388" Then
If Sheet9.Range("F9") <> "389" Then
If Sheet9.Range("F9") <> "390" Then
If Sheet9.Range("F9") <> "391" Then
If Sheet9.Range("F9") <> "392" Then
If Sheet9.Range("F9") <> "393" Then
If Sheet9.Range("F9") <> "394" Then
MsgBox ("Invalid Number")
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


Sheet9.Range("B4:E13").Select
    Selection.Copy
    Sheet15.Range("B2").PasteSpecial Paste:=xlPasteValues
    Range("H3:K13").Select
    Selection.Copy
    Sheet15.Range("H1").PasteSpecial Paste:=xlPasteValues
    Sheet9.Cells(9, 6).Copy
    Sheet15.Range("M1").PasteSpecial Paste:=xlPasteValues
    
    Sheet9.Range("B4:E13, H3:K13, F9").Select
    Selection.ClearContents




  If Sheet15.Range("M1") = "201" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B4:E13").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H3:K13").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "202-1" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B15:E24").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H14:K24").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "202-2" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B26:E35").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H25:K35").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "203" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B37:E46").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H36:K46").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "204" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B48:E57").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H47:K57").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "205" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B61:E70").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H60:K70").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "206" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B72:E81").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H71:K81").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "207" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B83:E92").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H82:K92").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "208" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B94:E103").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H93:K103").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "209" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B105:E114").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H104:K114").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "210" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B118:E127").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H117:K127").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "211" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B129:E138").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H128:K138").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "212-1" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B140:E149").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H139:K149").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "212-2" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B151:E160").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H150:K160").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "213" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B162:E171").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H161:K171").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "214-1" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B175:E184").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H174:K184").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "214-2" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B186:E195").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H185:K195").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "215" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B197:E206").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H196:K206").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "216-1" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B208:E217").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H207:K217").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "216-2" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B219:E228").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H218:K228").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "217" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B232:E241").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H231:K241").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "218-1" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B243:E252").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H242:K252").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "218-2" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B254:E263").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H253:K263").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "219" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B265:E274").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H264:K274").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "220-1" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B276:E285").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H275:K285").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "220-2" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B289:E298").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H288:K298").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "221" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B300:E309").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H299:K309").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "222-1" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B311:E320").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H310:K320").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "222-2" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B322:E331").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H321:K331").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "223" Then
Sheet15.Range("B2:E11").Copy
Sheet9.Range("B333:E342").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet9.Range("H332:K342").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "224" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B4:E13").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H3:K13").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "225-1" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B15:E24").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H14:K24").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "225-2" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B26:E35").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H25:K35").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "226" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B37:E46").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H36:K46").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "227-1" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B48:E57").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H47:K57").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "227-2" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B61:E70").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H60:K70").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "228" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B72:E81").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H71:K81").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "229-1" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B83:E92").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H82:K92").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "229-2" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B94:E103").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H93:K103").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "230" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B105:E114").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H104:K114").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "232" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B118:E127").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H117:K127").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "234" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B129:E138").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H128:K138").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "236" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B140:E149").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H139:K149").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "237" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B151:E160").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H150:K160").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "238" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B162:E171").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H161:K171").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "239" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B175:E184").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H174:K184").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "240" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B186:E195").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H185:K195").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "241" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B197:E206").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H196:K206").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "242" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B208:E217").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H207:K217").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "243" Then
Sheet15.Range("B2:E11").Copy
Sheet8.Range("B219:E228").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet8.Range("H218:K228").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "361" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B4:E13").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H3:K13").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "362" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B15:E24").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H14:K24").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "363" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B26:E35").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H25:K35").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "364" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B37:E46").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H36:K46").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "365" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B48:E57").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H47:K57").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "366" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B61:E70").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H60:K70").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "367" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B72:E81").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H71:K81").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "368" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B83:E92").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H82:K92").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "369" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B94:E103").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H93:K103").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "370" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B105:E114").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H104:K114").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "371" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B118:E127").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H117:K127").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "372" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B129:E138").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H128:K138").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "373" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B140:E149").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H139:K149").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "374" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B151:E160").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H150:K160").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "375" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B162:E171").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H161:K171").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "376" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B175:E184").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H174:K184").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "377" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B186:E195").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H185:K195").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "378" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B197:E206").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H196:K206").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "379" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B208:E217").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H207:K217").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "380" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B219:E228").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H218:K228").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "381" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B232:E241").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H231:K241").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "382" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B243:E252").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H242:K252").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "383" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B254:E263").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H253:K263").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "384" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B265:E274").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H264:K274").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "385" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B276:E285").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H275:K285").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "386" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B289:E298").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H288:K298").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "387" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B300:E309").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H299:K309").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "388" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B311:E320").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H310:K320").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "389" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B322:E331").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H321:K331").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "390" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B333:E342").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H332:K342").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "391" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B346:E355").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H345:K355").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "392" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B357:E366").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H356:K366").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "393" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B368:E377").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H367:K377").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "394" Then
Sheet15.Range("B2:E11").Copy
Sheet2.Range("B379:E388").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet2.Range("H378:K388").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "301" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B4:E13").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H3:K13").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "302-1" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B15:E24").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H14:K24").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "302-2" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B26:E35").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H25:K35").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "303" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B37:E46").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H36:K46").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "304" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B48:E57").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H47:K57").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "305" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B61:E70").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H60:K70").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "306" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B72:E81").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H71:K81").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "307" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B83:E92").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H82:K92").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "308" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B94:E103").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H93:K103").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "309" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B105:E114").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H104:K114").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "310" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B118:E127").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H117:K127").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "311" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B129:E138").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H128:K138").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "312-1" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B140:E149").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H139:K149").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "312-2" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B151:E160").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H150:K160").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "313" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B162:E171").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H161:K171").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "314-1" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B175:E184").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H174:K184").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "314-2" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B186:E195").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H185:K195").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "315" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B197:E206").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H196:K206").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "316-1" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B208:E217").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H207:K217").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "316-2" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B219:E228").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H218:K228").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "317" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B232:E241").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H231:K241").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "318-1" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B243:E252").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H242:K252").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "318-2" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B254:E263").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H253:K263").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "319" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B265:E274").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H264:K274").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "320-1" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B276:E285").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H275:K285").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "320-2" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B289:E298").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H288:K298").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "321" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B300:E309").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H299:K309").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "322-1" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B311:E320").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H310:K320").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "322-2" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B322:E331").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H321:K331").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "323" Then
Sheet15.Range("B2:E11").Copy
Sheet10.Range("B333:E342").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet10.Range("H332:K342").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "424" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B4:E13").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H3:K13").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "425-1" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B15:E24").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H14:K24").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "425-2" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B26:E35").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H25:K35").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "426" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B37:E46").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H36:K46").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "427-1" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B48:E57").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H47:K57").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "427-2" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B61:E70").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H60:K70").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "428" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B72:E81").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H71:K81").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "429-1" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B83:E92").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H82:K92").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "429-2" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B94:E103").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H93:K103").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "430" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B105:E114").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H104:K114").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "431-1" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B118:E127").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H117:K127").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "431-2" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B129:E138").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H128:K138").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "432" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B140:E149").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H139:K149").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "434" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B151:E160").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H150:K160").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "435-1" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B162:E171").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H161:K171").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "435-2" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B175:E184").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H174:K184").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "436" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B186:E195").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H185:K195").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "437" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B197:E206").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H196:K206").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "438" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B208:E217").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H207:K217").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "439" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B219:E228").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H218:K228").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "440" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B232:E241").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H231:K241").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "441" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B243:E252").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H242:K252").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "442" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B254:E263").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H253:K263").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "443" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B265:E274").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H264:K274").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "444" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B276:E285").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H275:K285").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "445-1" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B289:E298").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H288:K298").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


If Sheet15.Range("M1") = "445-2" Then
Sheet15.Range("B2:E11").Copy
Sheet6.Range("B300:E309").PasteSpecial Paste:=xlPasteValues
Sheet15.Range("H1:K11").Copy
Sheet6.Range("H299:K309").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If


For i = 1 To Sheets.Count
 Sheets(i).Protect
 Next i
 Sheet1.UnProtect
 Sheet7.UnProtect
 End If
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
I think you should explain a little more exactly what you want to happen. And I will see if I can provide a way to do what you want.

Please do not tell me how you want it done. Just tell me what you want the end result to be.

Like in the beginning you said:

using a button(hyperlink style)

I do not know what a
button(hyperlink style)​
is

So just tell me what your ultimate goal is and I will see what I can do.

Tell me a little about what this project does.

You said:
i want to be able to copy paste a particular area of info from one place to another via entering data(in this case a room number) into a cell and using a ​

Ok you enter a room number into sheet named "Master" Range "A1" for example

Now is this room number a sheet name?

Like if you enter 2 you want this data copied to sheet(2).

And where on sheet(2) should this data be entered?

So tell me where is the data on the master sheet and where do you want it pasted on sheet 2
 
Upvote 0
It appears as if we are always copying the same range from sheet15
And pasting into sheet 9
Is that true?

I have not read through all 200 or so lines of code

And on sheet nine we always copy it down 11 more lines depending on room number

But you also said this is only for one sheet

You need to explain the logic or the trend here.

And why all those if statements at the top of the script?

Like this:
If Sheet9.Range("F9") <> "379" Then
​

So if you only want people allowed to enter certain values then we should put a data validation list in F9 that only gives them certain numbers to choose from.
 
Upvote 0
It is always 11 more down in sets of 5 at which point there is a page break with a date header that pushes the first one one page at a space of 12

If Sheet9.Range("F9") <> "379" Then
Is to check for valid room entry
 
Upvote 0
Each room has a range of data at varying locations throughout the file. I want each room area to contain a button that ask for a room number then locates that room in the file and copies the data from location one to location two
 
Upvote 0
So why did you have:

If Sheet9.Range("F9") <> "201" Then
If Sheet9.Range("F9") <> "202-1" Then
If Sheet9.Range("F9") <> "202-2" Then
If Sheet9.Range("F9") <> "203" Then
If Sheet9.Range("F9") <> "204" Then
If Sheet9.Range("F9") <> "205" Then
If Sheet9.Range("F9") <> "206"


and on and on?
​
 
Upvote 0
Let me try to simplify... you have rooms A B C
Each with a button. Clicking the button for A brings a prompt up in which typing in B sends all data from range A to range B and clears the data from Range A.. using the button for C would allow C data to move to A or B
 
Upvote 0
My Aswer Is This said:
So why did you have:

If Sheet9.Range("F9") <> "201" Then
If Sheet9.Range("F9") <> "202-1" Then
If Sheet9.Range("F9") <> "202-2" Then
If Sheet9.Range("F9") <> "203" Then
If Sheet9.Range("F9") <> "204" Then
If Sheet9.Range("F9") <> "205" Then
If Sheet9.Range("F9") <> "206"


and on and on?
​
Click to expand...

This was the only way I could figure out how to check for valid rooms and reject invalid rooms
 
Upvote 0
You said:
but i need to do this for what ends up being about 140 rooms​

Having 140 buttons on about 5 different sheets that perform some sort of task seems like a very big challenge
 
Upvote 0
that is pretty much what i was afraid of. the reason i am trying to do this is that my co workers are incapable of just old copy pasting and stoping them from using Cut features still seems to not work as there are lots of equations in use and somehow even using indirect functions get messed up in just a few days time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
183
NdNoviceHlp
NdNoviceHlp
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
52
Logit
Logit
earthworm
  • Question Question
Autofit table in Outlook upon copying from Excel sheet using Macro
Replies
1
Views
163
KOKOSEK
KOKOSEK
nathanzumbaugh
  • Question Question
Add blank cells below pasted data from sheet to sheet using VBA-have working code, just need add row syntax
Replies
0
Views
167
nathanzumbaugh
nathanzumbaugh
G
  • Question Question
Slow VBA code
Replies
1
Views
312
Tupe77
T

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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