Update sheets dependent on excel cell

Guys

on design request form Cell T2 there is a picklist that shows individual sheets in the workbook what Im after is the code attached to look at the cell and move the data to the sheet at present its hardcoded to copy the data to the Scaffold Register but this is soon to be changed and dependant on what is chosen the script is ran and the data is moved to what ever sheet

Can you help


Sub CopyForm()

Dim lastrow As Long
Dim D As Long

    X = 2

MyLoop:
    X = X + 1
    If Sheets("Scaffold Register").Range("A" & X).Value = "" Then
                    Sheets("Scaffold Register").Range("A" & X).Value = Sheets("Design Request Form").Range("C8").Value 'Client
                    Sheets("Scaffold Register").Range("C" & X).Value = Sheets("Design Request Form").Range("I7").Value 'Location
                    Sheets("Scaffold Register").Range("F" & X).Value = Sheets("Design Request Form").Range("C13").Value 'Type of Scaffold
                    Sheets("Scaffold Register").Range("G" & X).Value = Sheets("Design Request Form").Range("C19").Value 'Scaffold Length
                    Sheets("Scaffold Register").Range("H" & X).Value = Sheets("Design Request Form").Range("C20").Value 'Scaffold Width
                    Sheets("Scaffold Register").Range("I" & X).Value = Sheets("Design Request Form").Range("I20").Value 'No of Working Lifts
                    Sheets("Scaffold Register").Range("J" & X).Value = Sheets("Design Request Form").Range("I19").Value 'No of Boarded Lifts
                    Sheets("Scaffold Register").Range("P" & X).Value = Sheets("Design Request Form").Range("H6").Value 'Date
                    Sheets("Scaffold Register").Range("Q" & X).Value = Sheets("Design Request Form").Range("J6").Value 'Time
                    Sheets("Scaffold Register").Range("R" & X).Value = Sheets("Design Request Form").Range("B2").Value 'PO NO
                    Sheets("Scaffold Register").Range("V" & X).Value = Sheets("Design Request Form").Range("C6").Value 'Requested By
                
        
 
        '<--end of insert
        'Sheets(MyForm).Select
        'Range("A1").Select
    Else
        GoTo MyLoop
    End If
    
    
        Sheets("Scaffold Register").Select
              Range("A1").Select
    
    
        lastrow = Sheets("Scaffold Register").[A65536].End(xlUp).Row

        For D = lastrow To 1 Step -1
            If Cells(D, 8) = "" Then
               Cells(D, 1).EntireRow.Delete
     End If

Next D

         Sheets("Design Request Form").Select
                     Range("B4").Select
                     
   Range("C6:C9,C11,I7,I9,I10,C15,C19,C20,C21,I19:I21,C27,C28,C33,C35,C37:C39,I38,J38,I39").Select
    Selection.ClearContents
    Range("B4").Select
   
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Open in new window

DarrenJacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Replace all instances of Sheets("Design Request Form") with Sheets(var_sheet)
Somewhere on top define
var_sheet = xx
where xx could be some input box or a cell reference, as long as the input is a string, and that string is the name of the sheet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DarrenJacksonAuthor Commented:
Nice one thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.