Update sheets dependent on excel cell


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

    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
        GoTo MyLoop
    End If
        Sheets("Scaffold Register").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
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Open in new window

Who is Participating?
KimputerConnect With a Mentor Commented:
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.
DarrenJacksonAuthor Commented:
Nice one thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.