Copy and paste value to another worksheet by VBA

1
votes

I am trying to work on VBA to copy and paste some cell values to the other worksheets in the same workbook.

In Order List worksheet format is as follows:

(Column A)   | (Column B)          | (Column C) | (Column D) | (Column E) | (Column F)
Product Code | Product Description | Price      | Quantity   | Net Amount | Sheet Name

I need to copy the Quantity in Order List sheet and paste to relevant sheet as shown in Column F.

For example, Product Code AAA has Quantity 10 with Sheet Name Art in Order List.

Product Code | Product Description | Price | Quantity | Net Amount | Sheet Name 
AAA            ...                   ...     5          ...          Art

I need a code to replace AAA's Quantity (Column G) in Art sheet to 10 instead of 5. The Product Code is in Column B of Art Sheet. 

(Column B)   | ...           | (Column G)
Product Code | Other headers | Quantity
AAA            ...             10           ' <---- REPLACE WITH 5

I have updated as following:

Dim j As Long, i As Long

j = 18

With Worksheets("Order List")

If Sheets("Order List").Range("F" & j) <> "" And Sheets("Order List").Range("A" & j) <> "" Then

 i = Worksheets(.Range("F" & j)).Columns("B").Find(What:=.Range("A" & j).Value, LookIn:=xlValues, LookAt:=xlWhole).Row

Sheets("Order List").Range("D" & j).Copy Destination:=Worksheets(Worksheets("Order List").Range("F" & j)).Range("G" & i)

j = j + 1

End If

There is no error message now, but not replace the quantity either in Art Sheet. Is the destination wrong? Could you please double check it for me? Many thanks

3
vbaexcel
You don't need to find a value for j unless the relevant sheet and the product code are both available, so don't try to use those values before you check that they are available. - YowE3K
@YowE3K I have updated the columns. Product Code in Column A, Quantity in Column D and Sheet Name is in Column F in Order List. The first row is 18 in Order List. Product Codes are in Column B in the other worksheets. I understand that the code you wrote is find the row number and I use this row number to find the relevant Column G in the other sheets (Art Sheet in this example). The relevant sheet and the product code are both available, so I assume the code should work. I have updated it and still see run time error "9". Could you please correct my code if it is wrong? Many thanks - J Ding
Your updated code is still trying to use Worksheets(.Range("F" & j)) before you test whether Sheets("Order List").Range("F" & j) <> "". So if it is blank, you are looking for the worksheet with no name. Don't try to use the values before you check that they are available - move the calculation of i inside the If statement where you will know that it will work. - YowE3K
@YowE3K I have updated it again as your advice. Hope I didn't misunderstand you. It is now showing Type Mismatch. Would you mind checking it again for me please? Many thanks - J Ding
It looks like this is one of the cases where the Value property is not used by default - .Range("F" & j) needs to be changed to .Range("F" & j).Value when being used as an index to the Worksheets collection. I'll update my answer accordingly. - YowE3K

3 Answers

1
votes

Try using the Find command, i.e. something like:

Dim j As Long, i As Long

j = 18

With Worksheets("Order List")
    If .Range("F" & j).Value <> "" And .Range("A" & j).Value <> "" Then
        i = Worksheets(.Range("F" & j).Value).Columns("B").Find(What:=.Range("A" & j).Value, LookIn:=xlValues, LookAt:=xlWhole).Row
        .Range("D" & j).Copy Destination:=Worksheets(.Range("F" & j).Value).Range("G" & i)
        'or, if you just want to copy the value, use
        'Worksheets(.Range("F" & j).Value).Range("G" & i).Value = .Range("D" & j).Value
        j = j + 1
    End If
End With

Notes:

  1. Assumes product code is in column A of the sheet you are trying to update.
  2. This will crash if the product code does not exist in column A of the relevant sheet. If that is a possibility then you will need to include the proper error checking to see whether the Find was successful (i.e. did not return Nothing) before attempting to get the Row property.
0
votes

Perhaps there is an easier, alternate solution. Instead of handling the troublesome multi-sheet order quantity search with VBA (and "putting" the new data on the order sheet), you could use the more-user-friendly worksheet Lookup function(s) on the Order List worksheet to "get" the data consolidated in one place from all the other sheets, and then a simple single column copy & paste when the time is right.

sample destination sheet

and

[sample data[2]

You can download my [simple] demo XLSM from JumpShare here to demonstrate what I mean by "put instead of get". Basically 1 lookup formula does all the searching for you, only 2 lines of code to copy over new data when required. {Note that the macro won't work on the online viewer.)

0
votes

VBA is not my forte, so I deconstructed your code a bit into the following so I could understand it better.

Dim j As Long, i As Long
Dim s As String
Dim ws As Worksheet

j = 18
Set ws = Worksheets("Order List")

s = ws.Range("A" & j).Value
i = Worksheets(ws.Range("F" & j)).Columns("B").Find(What:=s, LookIn:=xlValues, _
    LookAt:=xlWhole).Row

If ws.Range("F" & j) <> "" And ws.Range("A" & j) <> "" Then
  ws.Range("D" & j).Copy Destination:=Worksheets("F" & j).Range("G" & i)
  j = j + 1
End If

The one thing that jumped out at me was the last real line of code:

Destination:=Worksheets("F" & j).Range("G" & i)

This tells me there is a worksheet in your workbook named "Fx" (where x is a number). Is it possible you meant this?

Destination:=Worksheets(ws.Range("F" & j))