CODEWITHSUNDEEP
vbaexcel
J Ding
J Ding

Reputation: 33

Copy and paste value to another worksheet by VBA

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

Upvotes: 1

Views: 1355

Answers (3)

YowE3K
YowE3K

Reputation: 23974

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.

Upvotes: 1

Hambone
Hambone

Reputation: 16377

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))

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21619

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.)

Upvotes: 0

Related Questions