Copying cells from a range with offset method VBA

Question

Hello

I guess I am making it over complicated the problem I have or just missing something important. 

I have two sheets "Order" and "Form". Need to copy certain customer's name from "Order" to the "Form" range E18, date to "Form" E29 and print as many copies as it says in the form. 

 This is my code so far (but it is throwing me an error):

Option Explicit

Sub PrntForm()

Dim wsb As Worksheet, wsp As Worksheet, rng As Range, cell As Range

Set wsb = ThisWorkbook.Sheets("Order")

Set wsp = ThisWorkbook.Sheets("Form")

Set rng = wsb.Range("Customer")                                            ' Defined Range Sheet Form F16:F27

    For Each cell In rng

        If cell.Value = "A" Then

            cell.Copy wsp.Range("E18")                                            'Get the customer name from Form F16

            cell.Offset(0, -4).Copy wsp.Range("E29")                         'Get the Date   from Form B17

            wsp.PrintOut From:=1, _

            To:=cell.Offset(0, -2).Value, Copies:=2, Collate:=False    ' Nr of copies Form D17 = 18 copies

        End If

   Next cell

End Sub

Any help is appreciated

Nick.

Answer 1

I don't know why your particular line of code is failing. I have created a dummy workbook from the info you have provided and it works for me.

It is possible that you have a non visible character in there somewhere so try deleting the entire line (not just the code in the line but the entire line) and then re-type the line of code.

You can also try inserting the parameter "Destination:="  for the part that is to be pasted although it should work without it. 

Also a few tips for your VBA coding.

Try to use variables that will be meaningful so that when you have code with hundreds of lines it is easy to identify what the variable is used for. I would code something like the following where the worksheet variables and range variables are easy to identify to what they represent. The benefit of using the prefix ws for worksheet and rng for range is a debateable issue but it does ensure that reserved words are not used and I always use them. However, the rest of the variable should be identifiable as to what it represents and in the following example I have abbreviated the item they represent.

Sub PrntForm()

    Dim wsOrd As Worksheet, wsFrm As Worksheet, rngCust As Range, rngCell As Range

    Set wsOrd = ThisWorkbook.Sheets("Order")

    Set wsFrm = ThisWorkbook.Sheets("Form")

    Set rngCust = wsOrd.Range("Customer")   'Defined Range Sheet Form F16:F27

     For Each rngCell In rngCust

         If rngCell.Value = "A" Then

             rngCell.Copy Destination:=wsFrm.Range("E18")    'Get the customer name

             rngCell.Offset(0, -4).Copy Destination:=wsFrm.Range("E29")  'Get the Date

             wsFrm.PrintOut From:=1, To:=rngCell.Offset(0, -2).Value, Copies:=2, Collate:=False

         End If

    Next rngCell

End Sub

Answer 2

On what line of code are you getting the error and what is the error message?

Have you set a Print Area?

From your code I am not sure which sheet you want to print. The following line refers to the ActiveWindow.SelectedSheets but which sheet is the selected sheet?

ActiveWindow.SelectedSheets.PrintOut From:=1, _

            To:=cell.Offset(0, -2).Value, Copies:=2, Collate:=False 

Better to specify the worksheet like the following but I am not sure which sheet you want to print so edit the sheet variable if not correct.

wsb.PrintOut From:=1, To:=cell.Offset(0, -2).Value, Copies:=2, Collate:=False.

Also from your screen shot I think that the following line should reference the -4 column if you want the date and not -3.

cell.Offset(0, -3).Copy wsp.Range("E29")                         'Get the Date   from Form B17.

When using offset, the offset is counted by the number of times you need to press the arrow on the keyboard to get to the correct cell (either column of row) from the reference cell. In your case the reference column is F so if a cell is selected in column F then you would need to press the left arrow 4 times to get to column B with the Dates.

Feel free to get back to me if the above is not helping but I need to know which sheet you want to print.

Answer 3

Hi Ossie

Sorry mate this thing was driving me crazy at work, now I see that I missed a few hidden columns (C and E), you are right about the (-4) offset thing.

The code is stopping at  cell.Copy wsp.Range("E18") line

The sheet to be printed is the Form sheet,

Basically I just need to copy the name of a certain customer (A) from the order list to the Form sheet in cell E18, the delivery date from the order sheet to the Form sheet in cell E29 and print as many copies as it says in column D in Order Sheet, for example (From:=1, To:=18,)  for customer "A" . 

I can hard code it with copy paste, but the list changes very often and I need it to look down the list( Range Sheet Form F16:F27) for a certain customer.

I know it is a simple sub, but somehow I'm not getting it, and I **** hate it.

Thanks Buddy