VB copde help - please!

[Guest]

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 Phillips]

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?

 

[Guest]

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]

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?

 

[Guest]

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]

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