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
 
Sort by date Sort by votes
BiKeTa said:
This was the only way I could figure out how to check for valid rooms and reject invalid rooms
Click to expand...
Do you know what a data validation list is?

You could set one up so only certain numbers could be entered into a cell.
It would show you in the cell what numbers you could select from.

But now if you want 140 buttons then I guess you have changed what you want.
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
My Aswer Is This said:
Do you know what a data validation list is?

You could set one up so only certain numbers could be entered into a cell.
It would show you in the cell what numbers you could select from.

But now if you want 140 buttons then I guess you have changed what you want.
Click to expand...
i am somewhat familiar with data validation lists and have used them once. i will try to work out that part to clear up that portion of the VBA.

my horrible describing techniques is not helping you out much but let me try to pose one more question and that may give me a stepping stone to try something else
i am using a cell with a hyperlink to itself as a button to keep a phenomenon of disappearing buttons that has happened to me in the past. is there a way to select a range of data by using the cell the hyperlink is in as a reference point?

something like when the link is activated.... find range up so many rows and and columns away from the cell the hyperlink goes to?
 
Upvote 0
See I'm just here to try and help you. So if you already have your plan set up how you want it done but don't know how to do it. That can be difficult because we may know a lot easier way.

So on sheet9 I would actually give this sheet a name like Rooms 1-30

Where on the sheet is the number show for the room number?

Is it always in column B?

So see we could have a script look down column B looking for the room number.
Then if it find 25 in Range("B5") we could enter your data in cell C5 D5 and D6

Or something like that
We would tell the script to always put the data in one cell to the right of room number
Or one row down from room. See this way we have one script which always knows what to do.


So on the Master sheet all you would need to do is enter 25 into some cell and the script would know what to do.
 
Upvote 0
yes this is what i am trying to go for. below is an image of a couple of rooms. they are all set up exactly the same way just with different room numbers. the left column uses formulas which in the end store data from the previous 24 hours. the right column is the current day. i need to move data between this setup if you have any suggestions

https://www.dropbox.com/s/oc8z4n51d653mod/Untitled.jpg?dl=0
 
Upvote 0
I would suggest we use what is know as a sheet activation event
When you enter a value like 25 into Range("A1") a script automatically runs.

For example if you enter 25 into Range("A1") the script would search for room 25 and then do something.

No buttons needed.

In range("A1") you would have a data validation list where it would only have certain numbers shown and you would have to choose one of those in the list.
 
Upvote 0
i think i am starting to follow you. it is a little late in the night and it is all running together so maybe a fresh start in the morning may help. If i have more questions i will return here then to get clarification
 
Upvote 0
I looked at your image:

Since I see no column headers

I'm assuming room number is in column A and second room number is column F.

So when we run the script you want to copy just values from column B C D E and paste into columns H I J K

Can we just copy values or do we need formatting also.

Is this what your wanting?
And leave data in column B C D E the same as it is
 
Last edited:
Upvote 0
no left and right columns are the same room over a 48 hour period. more or less the data need to move up and down and from sheet to sheet based on were i want to data to go by typing in a room number. i am sorry i suck at explanation and am just taking up your time. i will keep working on it though.
 
Upvote 0
BiKeTa said:
i think i am starting to follow you. it is a little late in the night and it is all running together so maybe a fresh start in the morning may help. If i have more questions i will return here then to get clarification
Click to expand...

OK see you back tomorrow. But it is easier to help people if you check back in sooner then later. Some people here on this forum only check back in once a week or longer and that can cause me at least to forget what we were doing a week ago.
 
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
184
NdNoviceHlp
NdNoviceHlp
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
53
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,337
Members
452,637
Latest member
Ezio2866

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