VB copde help - please!
 

Hi,
I'm quite new to this kinda thing so go easy!
Ok, I have a workbook with 3 worksheets
1) Customer Name , ie John Smith
2) A selection of data copy/pasted from 'John Smith's' oder form into
Invoice data (this data is an entire row - which is row 35 or cells A35:AB35)
3) Invoice master form.

What I have is a macro button on my personal tool bar which I want the user
to be able to select anywhere in the workbook. A msg box comes up asking to
input name of customer who's invoice is to printed. That order form is
seached for , the data collected, pasted into the 'Invoice Data' worksheet,
then the actual invoice printed.
However the code I have isn't quite correct - I have tried to adapt it from
another similar one I have but I can't get this one to work.
Here's what I am using...............

Sub print_Invoice()
Dim i As Integer
Dim sSheetName As String
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets(sSheetName)
Set wks2 = ThisWorkbook.Worksheets("Invoive data")
Set wks3 = ThisWorkbook.Worksheets("Invoice")
sSheetName = InputBox("Please enter the Name of the Customer who's invoice
is to be printed, ie John Smith")
If sSheetName = 0 Then Exit Sub
On Error Resume Next
Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No Order found with the name of " & i & " , please try again! "
Exit Sub
End If
On Error GoTo err_handler
wks1.Cells("A35:AC35").EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


Can anybody offer me a solution ???
many many many thanks

There are some bits I don't understand.

Set wks1 = ThisWorkbook.Worksheets(sSheetName)

sSheetname is not yet set. Should it be, or should this be after the
workbook is chosen.

Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)

i is also not set her. What are you looking for?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Hi,
I'm quite new to this kinda thing so go easy!
Ok, I have a workbook with 3 worksheets
1) Customer Name , ie John Smith
2) A selection of data copy/pasted from 'John Smith's' oder form into
Invoice data (this data is an entire row - which is row 35 or cells
A35:AB35)
3) Invoice master form.

What I have is a macro button on my personal tool bar which I want the
user
to be able to select anywhere in the workbook. A msg box comes up asking
to
input name of customer who's invoice is to printed. That order form is
seached for , the data collected, pasted into the 'Invoice Data'
worksheet,
then the actual invoice printed.
However the code I have isn't quite correct - I have tried to adapt it
from
another similar one I have but I can't get this one to work.
Here's what I am using...............

Sub print_Invoice()
Dim i As Integer
Dim sSheetName As String
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets(sSheetName)
Set wks2 = ThisWorkbook.Worksheets("Invoive data")
Set wks3 = ThisWorkbook.Worksheets("Invoice")
sSheetName = InputBox("Please enter the Name of the Customer who's invoice
is to be printed, ie John Smith")
If sSheetName = 0 Then Exit Sub
On Error Resume Next
Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No Order found with the name of " & i & " , please try again!
"
Exit Sub
End If
On Error GoTo err_handler
wks1.Cells("A35:AC35").EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


Can anybody offer me a solution ???
many many many thanks

Bob,
If I'm honest - most of this I don't understand - as I said I'm a little bit
of a novice when it comes to this kind of stuff. This is wht I also added an
explanation of what I wanted to achieve. The code given was copyed from
something similar to another workbook that I have - it works fine, and I have
attempted to alter it to do something else - without much sucess it would
seem.

sSheetname will be the 'name' of the customer entered in the msg pop up box
by the user. A search of all the worksheets is then carried out to find a
match.
As for the i value - I am not sure what this is for, I think it is looking
down the entire 'A' column of the Invoice Data worksheet to find the name
match for the one input.
Hope this helps, and I hope more you can solve this for me.
Thanks for now



"Bob Phillips" wrote:

There are some bits I don't understand.

Set wks1 = ThisWorkbook.Worksheets(sSheetName)

sSheetname is not yet set. Should it be, or should this be after the
workbook is chosen.

Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)

i is also not set her. What are you looking for?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Hi,
I'm quite new to this kinda thing so go easy!
Ok, I have a workbook with 3 worksheets
1) Customer Name , ie John Smith
2) A selection of data copy/pasted from 'John Smith's' oder form into
Invoice data (this data is an entire row - which is row 35 or cells
A35:AB35)
3) Invoice master form.

What I have is a macro button on my personal tool bar which I want the
user
to be able to select anywhere in the workbook. A msg box comes up asking
to
input name of customer who's invoice is to printed. That order form is
seached for , the data collected, pasted into the 'Invoice Data'
worksheet,
then the actual invoice printed.
However the code I have isn't quite correct - I have tried to adapt it
from
another similar one I have but I can't get this one to work.
Here's what I am using...............

Sub print_Invoice()
Dim i As Integer
Dim sSheetName As String
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets(sSheetName)
Set wks2 = ThisWorkbook.Worksheets("Invoive data")
Set wks3 = ThisWorkbook.Worksheets("Invoice")
sSheetName = InputBox("Please enter the Name of the Customer who's invoice
is to be printed, ie John Smith")
If sSheetName = 0 Then Exit Sub
On Error Resume Next
Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No Order found with the name of " & i & " , please try again!
"
Exit Sub
End If
On Error GoTo err_handler
wks1.Cells("A35:AC35").EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


Can anybody offer me a solution ???
many many many thanks

Anthony,

It looks to me that the find is unnecessary, as the user specifies which
customer.

Is the data on each customer sheet in the same place? Assuming so, can you
tell us what needs to be copied from say John Smith (cell references as well
as explanation), and where it goes on the Invoice sheet. And which should it
go to Invoice or Invoice data?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Bob,
If I'm honest - most of this I don't understand - as I said I'm a little
bit
of a novice when it comes to this kind of stuff. This is wht I also added
an
explanation of what I wanted to achieve. The code given was copyed from
something similar to another workbook that I have - it works fine, and I
have
attempted to alter it to do something else - without much sucess it would
seem.

sSheetname will be the 'name' of the customer entered in the msg pop up
box
by the user. A search of all the worksheets is then carried out to find a
match.
As for the i value - I am not sure what this is for, I think it is looking
down the entire 'A' column of the Invoice Data worksheet to find the name
match for the one input.
Hope this helps, and I hope more you can solve this for me.
Thanks for now



"Bob Phillips" wrote:

There are some bits I don't understand.

Set wks1 = ThisWorkbook.Worksheets(sSheetName)

sSheetname is not yet set. Should it be, or should this be after the
workbook is chosen.

Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)

i is also not set her. What are you looking for?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Hi,
I'm quite new to this kinda thing so go easy!
Ok, I have a workbook with 3 worksheets
1) Customer Name , ie John Smith
2) A selection of data copy/pasted from 'John Smith's' oder form into
Invoice data (this data is an entire row - which is row 35 or cells
A35:AB35)
3) Invoice master form.

What I have is a macro button on my personal tool bar which I want the
user
to be able to select anywhere in the workbook. A msg box comes up
asking
to
input name of customer who's invoice is to printed. That order form is
seached for , the data collected, pasted into the 'Invoice Data'
worksheet,
then the actual invoice printed.
However the code I have isn't quite correct - I have tried to adapt it
from
another similar one I have but I can't get this one to work.
Here's what I am using...............

Sub print_Invoice()
Dim i As Integer
Dim sSheetName As String
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets(sSheetName)
Set wks2 = ThisWorkbook.Worksheets("Invoive data")
Set wks3 = ThisWorkbook.Worksheets("Invoice")
sSheetName = InputBox("Please enter the Name of the Customer who's
invoice
is to be printed, ie John Smith")
If sSheetName = 0 Then Exit Sub
On Error Resume Next
Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No Order found with the name of " & i & " , please try
again!
"
Exit Sub
End If
On Error GoTo err_handler
wks1.Cells("A35:AC35").EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


Can anybody offer me a solution ???
many many many thanks

Bob,
Yes the data to be copy/pasted in say, 'John Smith' worksheet will always be
in the same cells - which are A35:AC35 this is basicaly a copy of all the
data in the customer order form compacted into one row.
This whole row of data (cells A35:AC35) is then pasted onto the 'Invoice
Data' worksheet from A2:AC2.
The Invoice master sheet is then already set up to take the data from the
Invoice data sheet so nothing needs to be done there except print it when all
the data has been collected.
Maybe a little example might expalin it better,
User choses customer 'Anthony' (via msg input box) to print this invoice.
the worksheet with the name 'Anthony' is searched for and once found cells
A35:AC35 are copied and then pasted into cells A2:AC2.
From this pasted data in the invoice data sheet the invoice master will pick
up which data it requires and then the Invoice is printed - maybe with a
simpls msg box saying "thanks - Anthony's invoice is now printing"

Does this make any better sense - hey and many thanks for this so far Bob
Cheers
Anthony

"Bob Phillips" wrote:

Anthony,

It looks to me that the find is unnecessary, as the user specifies which
customer.

Is the data on each customer sheet in the same place? Assuming so, can you
tell us what needs to be copied from say John Smith (cell references as well
as explanation), and where it goes on the Invoice sheet. And which should it
go to Invoice or Invoice data?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Bob,
If I'm honest - most of this I don't understand - as I said I'm a little
bit
of a novice when it comes to this kind of stuff. This is wht I also added
an
explanation of what I wanted to achieve. The code given was copyed from
something similar to another workbook that I have - it works fine, and I
have
attempted to alter it to do something else - without much sucess it would
seem.

sSheetname will be the 'name' of the customer entered in the msg pop up
box
by the user. A search of all the worksheets is then carried out to find a
match.
As for the i value - I am not sure what this is for, I think it is looking
down the entire 'A' column of the Invoice Data worksheet to find the name
match for the one input.
Hope this helps, and I hope more you can solve this for me.
Thanks for now



"Bob Phillips" wrote:

There are some bits I don't understand.

Set wks1 = ThisWorkbook.Worksheets(sSheetName)

sSheetname is not yet set. Should it be, or should this be after the
workbook is chosen.

Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)

i is also not set her. What are you looking for?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Hi,
I'm quite new to this kinda thing so go easy!
Ok, I have a workbook with 3 worksheets
1) Customer Name , ie John Smith
2) A selection of data copy/pasted from 'John Smith's' oder form into
Invoice data (this data is an entire row - which is row 35 or cells
A35:AB35)
3) Invoice master form.

What I have is a macro button on my personal tool bar which I want the
user
to be able to select anywhere in the workbook. A msg box comes up
asking
to
input name of customer who's invoice is to printed. That order form is
seached for , the data collected, pasted into the 'Invoice Data'
worksheet,
then the actual invoice printed.
However the code I have isn't quite correct - I have tried to adapt it
from
another similar one I have but I can't get this one to work.
Here's what I am using...............

Sub print_Invoice()
Dim i As Integer
Dim sSheetName As String
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets(sSheetName)
Set wks2 = ThisWorkbook.Worksheets("Invoive data")
Set wks3 = ThisWorkbook.Worksheets("Invoice")
sSheetName = InputBox("Please enter the Name of the Customer who's
invoice
is to be printed, ie John Smith")
If sSheetName = 0 Then Exit Sub
On Error Resume Next
Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No Order found with the name of " & i & " , please try
again!
"
Exit Sub
End If
On Error GoTo err_handler
wks1.Cells("A35:AC35").EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


Can anybody offer me a solution ???
many many many thanks

Anthony,

Perfect sense. Much as I thought, just added the detail.

Sub print_Invoice()
Dim i As Integer
Dim sSheetName As String
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks2 = ThisWorkbook.Worksheets("Invoice data")
sSheetName = InputBox("Please enter the Name of the Customer " & _
"who's invoice is to be printed, ie John Smith")
If sSheetName = "" Then Exit Sub
Set wks1 = Worksheets(sSheetName)
wks1.Range("A35:AC35").Copy Destination:=wks2.Cells(2, 1)

wks2.PrintPreview
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub




--
HTH

Bob Phillips

"Anthony" wrote in message
...
Bob,
Yes the data to be copy/pasted in say, 'John Smith' worksheet will always
be
in the same cells - which are A35:AC35 this is basicaly a copy of all the
data in the customer order form compacted into one row.
This whole row of data (cells A35:AC35) is then pasted onto the 'Invoice
Data' worksheet from A2:AC2.
The Invoice master sheet is then already set up to take the data from the
Invoice data sheet so nothing needs to be done there except print it when
all
the data has been collected.
Maybe a little example might expalin it better,
User choses customer 'Anthony' (via msg input box) to print this invoice.
the worksheet with the name 'Anthony' is searched for and once found cells
A35:AC35 are copied and then pasted into cells A2:AC2.
From this pasted data in the invoice data sheet the invoice master will
pick
up which data it requires and then the Invoice is printed - maybe with a
simpls msg box saying "thanks - Anthony's invoice is now printing"

Does this make any better sense - hey and many thanks for this so far Bob
Cheers
Anthony

"Bob Phillips" wrote:

Anthony,

It looks to me that the find is unnecessary, as the user specifies which
customer.

Is the data on each customer sheet in the same place? Assuming so, can
you
tell us what needs to be copied from say John Smith (cell references as
well
as explanation), and where it goes on the Invoice sheet. And which
should it
go to Invoice or Invoice data?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Bob,
If I'm honest - most of this I don't understand - as I said I'm a
little
bit
of a novice when it comes to this kind of stuff. This is wht I also
added
an
explanation of what I wanted to achieve. The code given was copyed
from
something similar to another workbook that I have - it works fine, and
I
have
attempted to alter it to do something else - without much sucess it
would
seem.

sSheetname will be the 'name' of the customer entered in the msg pop
up
box
by the user. A search of all the worksheets is then carried out to
find a
match.
As for the i value - I am not sure what this is for, I think it is
looking
down the entire 'A' column of the Invoice Data worksheet to find the
name
match for the one input.
Hope this helps, and I hope more you can solve this for me.
Thanks for now



"Bob Phillips" wrote:

There are some bits I don't understand.

Set wks1 = ThisWorkbook.Worksheets(sSheetName)

sSheetname is not yet set. Should it be, or should this be after the
workbook is chosen.

Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)

i is also not set her. What are you looking for?

--
HTH

Bob Phillips

"Anthony" wrote in message
...
Hi,
I'm quite new to this kinda thing so go easy!
Ok, I have a workbook with 3 worksheets
1) Customer Name , ie John Smith
2) A selection of data copy/pasted from 'John Smith's' oder form
into
Invoice data (this data is an entire row - which is row 35 or
cells
A35:AB35)
3) Invoice master form.

What I have is a macro button on my personal tool bar which I want
the
user
to be able to select anywhere in the workbook. A msg box comes up
asking
to
input name of customer who's invoice is to printed. That order
form is
seached for , the data collected, pasted into the 'Invoice Data'
worksheet,
then the actual invoice printed.
However the code I have isn't quite correct - I have tried to
adapt it
from
another similar one I have but I can't get this one to work.
Here's what I am using...............

Sub print_Invoice()
Dim i As Integer
Dim sSheetName As String
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets(sSheetName)
Set wks2 = ThisWorkbook.Worksheets("Invoive data")
Set wks3 = ThisWorkbook.Worksheets("Invoice")
sSheetName = InputBox("Please enter the Name of the Customer who's
invoice
is to be printed, ie John Smith")
If sSheetName = 0 Then Exit Sub
On Error Resume Next
Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No Order found with the name of " & i & " , please try
again!
"
Exit Sub
End If
On Error GoTo err_handler
wks1.Cells("A35:AC35").EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

wks3.PrintOut
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


Can anybody offer me a solution ???
many many many thanks