copy values to another sheet based on cell value

[myeung990]

hello im trying to copy values in some cells to another worksheet whose cell name is based on the value in e1.
Example value in e1=10mm so when my macro works the items i want to copy will be placed in worksheet named "10mm"

i have a working macro where the worksheet "demand log" is fixed i want to change it so that i can specify which worksheet i want to copy the items to.

Sub Third_Command()
Dim Rowd As Integer
Dim rowu As Integer


Update = MsgBox("Confirm?", vbYesNo, "Update")
If Update = vbYes Then

For rowu = 5 To 11
Rowd = 5

Do Until Worksheets("Demand Log").Cells(Row, 1) = ""
If Worksheets("Update").Cells(rowu, 1).Value = Worksheets("Demand Log").Cells(Row, 1) Then
Exit Do
Else
Rowd = Rowd + 1
End If
Loop
If Worksheets("Demand Log").Cells(Rowd, 1) = "" And Worksheets("Update").Cells(rowu, 14) <> "" Then
Worksheets("Demand Log").Cells(Rowd, 2).Value = Worksheets("Update").Cells(3, 16).Value
Worksheets("Demand Log").Cells(Rowd, 3).Value = Worksheets("Update").Cells(rowu, 14).Value
Worksheets("Demand Log").Cells(Rowd, 4).Value = Worksheets("Update").Cells(rowu, 7).Value
Worksheets("Demand Log").Cells(Rowd, 5).Value = Worksheets("Update").Cells(rowu, 13).Value
Worksheets("Demand Log").Cells(Rowd, 6).Value = Worksheets("Update").Cells(rowu, 2).Value
Worksheets("Demand Log").Cells(Rowd, 7).Value = Worksheets("Update").Cells(rowu, 3).Value
Worksheets("Demand Log").Cells(Rowd, 8).Value = Worksheets("Update").Cells(rowu, 4).Value


End If
Next rowu
End If
End Sub

what should replace Worksheets("Demand Log")?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Third_Command()
   Dim Rowd As Integer
   Dim rowu As Integer
   Dim Ws As Worksheet
   
   Update = MsgBox("Confirm?", vbYesNo, "Update")
   If Update = vbYes Then
      Set Ws = Sheets(Sheets("Update").Range("E1").Value)
      For rowu = 5 To 11
         Rowd = 5
         
         Do Until Ws.Cells(Row, 1) = ""
            If Worksheets("Update").Cells(rowu, 1).Value = Ws.Cells(Row, 1) Then
               Exit Do
            Else
               Rowd = Rowd + 1
            End If
         Loop
         If Ws.Cells(Rowd, 1) = "" And Worksheets("Update").Cells(rowu, 14) <> "" Then
            Ws.Cells(Rowd, 2).Value = Worksheets("Update").Cells(3, 16).Value
            Ws.Cells(Rowd, 3).Value = Worksheets("Update").Cells(rowu, 14).Value
            Ws.Cells(Rowd, 4).Value = Worksheets("Update").Cells(rowu, 7).Value
            Ws.Cells(Rowd, 5).Value = Worksheets("Update").Cells(rowu, 13).Value
            Ws.Cells(Rowd, 6).Value = Worksheets("Update").Cells(rowu, 2).Value
            Ws.Cells(Rowd, 7).Value = Worksheets("Update").Cells(rowu, 3).Value
            Ws.Cells(Rowd, 8).Value = Worksheets("Update").Cells(rowu, 4).Value
            
         
         End If
      Next rowu
   End If
End Sub

 

[myeung990]

Hi & welcome to MrExcel.
How about

Sub Third_Command()
   Dim Rowd As Integer
   Dim rowu As Integer
   Dim Ws As Worksheet
   
   Update = MsgBox("Confirm?", vbYesNo, "Update")
   If Update = vbYes Then
      Set Ws = Sheets(Sheets("Update").Range("E1").Value)
      For rowu = 5 To 11
         Rowd = 5
         
         Do Until Ws.Cells(Row, 1) = ""
            If Worksheets("Update").Cells(rowu, 1).Value = Ws.Cells(Row, 1) Then
               Exit Do
            Else
               Rowd = Rowd + 1
            End If
         Loop
         If Ws.Cells(Rowd, 1) = "" And Worksheets("Update").Cells(rowu, 14) <> "" Then
            Ws.Cells(Rowd, 2).Value = Worksheets("Update").Cells(3, 16).Value
            Ws.Cells(Rowd, 3).Value = Worksheets("Update").Cells(rowu, 14).Value
            Ws.Cells(Rowd, 4).Value = Worksheets("Update").Cells(rowu, 7).Value
            Ws.Cells(Rowd, 5).Value = Worksheets("Update").Cells(rowu, 13).Value
            Ws.Cells(Rowd, 6).Value = Worksheets("Update").Cells(rowu, 2).Value
            Ws.Cells(Rowd, 7).Value = Worksheets("Update").Cells(rowu, 3).Value
            Ws.Cells(Rowd, 8).Value = Worksheets("Update").Cells(rowu, 4).Value
            
         
         End If
      Next rowu
   End If
End Sub

thank you sir! your code worked perfectly

 

[Fluff]

Glad to help & thanks for the feedback