Easy VB code question

[Anthony]

Hi,
I'm the novice so I'm sure this will be easy for you guys.
Ok I have a macro to create a new worksheet for a new order placed. The
worksheet is named from the input data from cell B2.
The code will copy some data into the new worksheet and then use a message
box to acknowledge that the order has been placed.
What I can't get to work is that I want this msg box to be shown back in
the original 'order form' worksheet and not the newly created one.
ie I want all the selected data from the order to be pasted into the new
worksheet - and the only notice that this has been done will be the msg box !
hope I have made that clear, and here is my code

Sub NewSheet()
Dim x As Worksheet
Dim y As Worksheet
Dim z As Variant

Set x = ActiveSheet
With Sheets.Add
ActiveSheet.Name = x.Range("B2")
Set y = ActiveSheet

z = Array(x.Range("B2"), x.Range("B4")

y.Range("B3") = z(0) 'NAME
y.Range("D3") = z(1) 'BOOKING REF

End With
MsgBox "Thank You, Order Created Successfully !", vbInformation


End Sub

many thanks

[Chad]

before your msgbox write Sheets("Sheet 1").select and replace the Sheet 1
text with whatever the sheet name that you want to select.

"Anthony" wrote:

> Hi,
> I'm the novice so I'm sure this will be easy for you guys.
> Ok I have a macro to create a new worksheet for a new order placed. The
> worksheet is named from the input data from cell B2.
> The code will copy some data into the new worksheet and then use a message
> box to acknowledge that the order has been placed.
> What I can't get to work is that I want this msg box to be shown back in
> the original 'order form' worksheet and not the newly created one.
> ie I want all the selected data from the order to be pasted into the new
> worksheet - and the only notice that this has been done will be the msg box !
> hope I have made that clear, and here is my code
>
> Sub NewSheet()
> Dim x As Worksheet
> Dim y As Worksheet
> Dim z As Variant
>
> Set x = ActiveSheet
> With Sheets.Add
> ActiveSheet.Name = x.Range("B2")
> Set y = ActiveSheet
>
> z = Array(x.Range("B2"), x.Range("B4")
>
> y.Range("B3") = z(0) 'NAME
> y.Range("D3") = z(1) 'BOOKING REF
>
> End With
> MsgBox "Thank You, Order Created Successfully !", vbInformation
>
>
> End Sub
>
> many thanks
>
>
>

[Anthony]

Brill, thanks Chad

"Chad" wrote:

> before your msgbox write Sheets("Sheet 1").select and replace the Sheet 1
> text with whatever the sheet name that you want to select.
>
> "Anthony" wrote:
>
> > Hi,
> > I'm the novice so I'm sure this will be easy for you guys.
> > Ok I have a macro to create a new worksheet for a new order placed. The
> > worksheet is named from the input data from cell B2.
> > The code will copy some data into the new worksheet and then use a message
> > box to acknowledge that the order has been placed.
> > What I can't get to work is that I want this msg box to be shown back in
> > the original 'order form' worksheet and not the newly created one.
> > ie I want all the selected data from the order to be pasted into the new
> > worksheet - and the only notice that this has been done will be the msg box !
> > hope I have made that clear, and here is my code
> >
> > Sub NewSheet()
> > Dim x As Worksheet
> > Dim y As Worksheet
> > Dim z As Variant
> >
> > Set x = ActiveSheet
> > With Sheets.Add
> > ActiveSheet.Name = x.Range("B2")
> > Set y = ActiveSheet
> >
> > z = Array(x.Range("B2"), x.Range("B4")
> >
> > y.Range("B3") = z(0) 'NAME
> > y.Range("D3") = z(1) 'BOOKING REF
> >
> > End With
> > MsgBox "Thank You, Order Created Successfully !", vbInformation
> >
> >
> > End Sub
> >
> > many thanks
> >
> >
> >