VBA copying from various cells from various worksheets to one worksheet

Question

I am trying to copy values from a few worksheets, different cells in these worksheets. I was wondering if there is a way to simplify the code instead of the normal copy and paste. This is the code i have been using and its rather long.

I was thinking if there is a function that allows me to tell vba to copy and paste. Then I define a matching copy-paste cells. E.g. B2 - D3 B4 - D4 etc

Worksheets(stockcode & "_IS_" & marketcode).Range("B2").Copy
Worksheets(stockcode & "_Stock ratio_" & marketcode).Range("D3").PasteSpecial
Worksheets(stockcode & "_IS_" & marketcode).Range("B4").Copy
Worksheets(stockcode & "_Stock ratio_" & marketcode).Range("D4").PasteSpecial
Worksheets(stockcode & "_IS_" & marketcode).Range("B15").Copy
Worksheets(stockcode & "_Stock ratio_" & marketcode).Range("D5").PasteSpecial

Answer 1

This might help.

Dim ws as Worksheet
Set ws = Worksheets(stockcode & "_Stock ratio_" & marketcode)
With Worksheets(stockcode & "_IS_" & marketcode)
    .Range("B2").Copy Destination:=ws.Range("D3")
    .Range("B4").Copy Destination:=ws.Range("D4")
    .Range("B15").Copy Destination:=ws.Range("D5")
End With
Set ws = Nothing

If the value is all you need to carry across then this alternative method may be sufficient.

Dim ws as Worksheet
Set ws = Worksheets(stockcode & "_IS_" & marketcode)
With Worksheets(stockcode & "_Stock ratio_" & marketcode)
    .Range("D3") = ws.Range("B2").Value
    .Range("D4") = ws.Range("B4").Value
    .Range("D5") = ws.Range("B15").Value
End With
Set ws = Nothing

The first uses With ... End With to reference the IS worksheet. The second uses the same method to reference the Stock Ratio worksheet. When inside a With ... End With you prefix references with a period (aka full stop) to gain the parent relationship.

It is probably worthwhile to use With ... End With to reference one worksheet and assign the second to a variable. Edited above to note that second method.

Answer 2

You can create your own procedures to do just about anything you can imagine. You're code can be simplified considerably.

First, we will define a source worksheet and a destination worksheet. We do this to make our code easier to read, interpret, and maintain (there will now only be ONE place where you need to fuss with this messy concatenation: Worksheets(stockcode & "_Stock ratio_" & marketcode).

Dim srcSheet as Worksheet
Dim destSheet as Worksheet

Set srcSheet = Worksheets(stockcode & "_IS_" & marketcode)
Set destSheet = Worksheets(stockcode & "_Stock ratio_" & marketcode)

Now we can just use a Value assignment statement, since you only are concerned with pasting values, you can simply do this:

destSheet.Range("D3").Value = srcSheet.Range("B2").Value
destSheet.Range("D4").Value = srcSheet.Range("B3").Value
destSheet.Range("D5").Value = srcSheet.Range("B15").Value

This entirely avoids the Copy and Paste/PasteSpecial methods, and should be faster, too.

You could create a macro that puts this all together and gets a little fancier, it ultimately depends on your use case and how you want to input data or whether that is even necessary (often it is not, as there are logical rules that determine the relationship between sheets, which can be constructed in to code/syntax, etc.)

If you are looking to simply input the information, you can use an Application.InputBox, here's a simple example:

Sub TransferValues()
Dim src as Range
Dim dest as Range

Set src = Application.InputBox("Source?", Type:=8)
Set dest = Application.InputBox("Destination?", Type:=8)

dest.Value = src.Value
End Sub

Of course, this is not really much faster (if any) than doing copy/paste manually, but it serves to illustrate the concept you may be able to implement.