Macro MsgBox Help

[kermitfrog123]

I'm hoping someone can help me?

I have recorded the below macro and have assigned to a button on my worksheet. My Macro copies information from the sales sheet and pastes into a creation sheet which is then printed. #

What I want to do is, when the user clicks the button to run the macro I would like to specify which row number is used with an input msgbox or something. In the example below you can see row 72 is being used. Information is then copied from various columns all within row 72.

Is this possible?

Many Thanks

Sub Macro4()
'
' Macro4 Macro
'

'
    Range("C72").Select
    Selection.Copy
    Sheets("Creation").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sales").Select
    Range("D72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Creation").Select
    Range("B10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sales").Select
    Range("E72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Creation").Select
    Range("F10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sales").Select
    Range("F72:H72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Creation").Select
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[B___P]

after a little editing


Sub Macro4()
'
' Macro4 Macro
'

'
Myrow = CLng(InputBox("enter Row Number"))
Cells(Myrow, 3).Select
Selection.Copy
Sheets("Creation").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sales").Select
Cells(Myrow, 4).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Creation").Select
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sales").Select
Cells(Myrow, 5).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Creation").Select
Range("F10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sales").Select
Range(Cells(Myrow, 6), Cells(Myrow, 8)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Creation").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub



Bye

 

[circledchicken]

Hi,

Another alternative might be something like this:

Sub example()
    
    Dim vArr    As Variant
    Dim lInp    As Long


    ' user prompt for row number
    lInp = Application.InputBox( _
             Prompt:="Enter row number", _
             Title:="Row", _
             Type:=1)
    
    ' get input values from cells
    vArr = Sheets("Sales").Range("C" & lInp & ":H" & lInp).Value2
    
    ' print output values to cells
    With Sheets("Creation")
        .Range("B3").Value2 = vArr(1, 1)
        .Range("B10").Value2 = vArr(1, 2)
        .Range("F10").Value2 = vArr(1, 3)
        .Range("B5").Value2 = vArr(1, 4)
        .Range("C5").Value2 = vArr(1, 5)
        .Range("D5").Value2 = vArr(1, 6)
    End With


End Sub

 

[My Aswer Is This]

Try this:

Sub Modified()
Dim ans As String
Sheets("Sales").Activate
ans = InputBox("What Row")
Cells(ans, 3).Copy Destination:=Sheets("Creation").Cells(3, 2)
Cells(ans, 4).Copy Destination:=Sheets("Creation").Cells(10, 2)
Cells(ans, 5).Copy Destination:=Sheets("Creation").Cells(10, 6)
Range("F" & ans & ":H" & ans).Copy Destination:=Sheets("Creation").Cells(5, 2)
End Sub

 

[kermitfrog123]

Thank You so Much. This worked perfectly

 

[My Aswer Is This]

Which script worked perfectly for you. There are several shown here.

Thank You so Much. This worked perfectly

 

[kermitfrog123]

Which script worked perfectly for you. There are several shown here.

Sorry it was the first reply that worked. I didnt see the other answers until just. When I replied they werent there.