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?
 
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.
0
 
DarrenJacksonAuthor Commented:
Nice one thanks
0
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.