Vfrdtyky

Being new to VBA I would love some inputs to this code, to improve the speed of it... It doesn't feel so "VBA"-ish currently; however the "result" of the code correct...

Sub Rigtig()



Set Marketshare = Sheets("Output").Range("p40:p50")

'Select.

Sheets("Output").Select

    Cells(38, 17).Copy

Sheets("Input").Select

   Cells(33, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues



Sheets("Output").Select

Marketshare.Cells(1, 1).Copy

Sheets("Input").Select

    Cells(23, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Output").Select

    Cells(40, 17).Copy

    Cells(40, 17).Select

    Selection.PasteSpecial Paste:=xlPasteValues

Marketshare.Cells(2, 1).Copy

Sheets("Input").Select

    Cells(23, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Output").Select

    Cells(41, 17).Copy

    Cells(41, 17).Select

    Selection.PasteSpecial Paste:=xlPasteValues

    Marketshare.Cells(3, 1).Copy

Sheets("Input").Select

    Cells(23, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues





Sheets("Output").Select

    Cells(38, 18).Copy

Sheets("Input").Select

   Cells(33, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues



Sheets("Output").Select

Marketshare.Cells(1, 1).Copy

Sheets("Input").Select

    Cells(23, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Output").Select

    Cells(40, 18).Copy

    Cells(40, 18).Select

    Selection.PasteSpecial Paste:=xlPasteValues



Sheets("Output").Select

    Cells(38, 19).Copy

Sheets("Input").Select

   Cells(33, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues



Sheets("Output").Select

Marketshare.Cells(1, 1).Copy

Sheets("Input").Select

    Cells(23, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues

Sheets("Output").Select

    Cells(40, 19).Copy

    Cells(40, 19).Select

    Selection.PasteSpecial Paste:=xlPasteValues

Marketshare.Cells(2, 1).Copy

Sheets("Input").Select

    Cells(23, 28).Select

    Selection.PasteSpecial Paste:=xlPasteValues

I would like to do the same "copy paste" approx 10 times in rows, and then change the column.

Thanks in advance

Best

Valdemar

As @Glitch_Doctor said - if it's just the values you're after you can do this "this cell = that cell" rather than copy/paste.

To shorten your code and make it a bit more "VBA"-ish you could put your cell reference pairs into an array and step through the array:

Sub Test()



    Dim vAddresses As Variant

    Dim vRef As Variant



    vAddresses = Array( _

        Array("Q38", "AB33"), _

        Array("A1", "AB23"))



    For Each vRef In vAddresses

        Worksheets("Input").Range(vRef(1)) = Worksheets("Output").Range(vRef(0))

    Next vRef



End Sub  

You could also use a With...End With block so you don't have to use the sheet name each time:

Sub Test1()



    With Worksheets("Output")

        Worksheets("Input").Cells(33, 28) = .Cells(38, 17)

    End With



End Sub 

If you want to copy everything (formula, formatting) then use Copy & paste in a single line:

Sub Test2()



    With Worksheets("Output")

        Worksheets("Input").Cells(33, 28).Copy Destination:=.Cells(38, 17)

    End With



End Sub

So I want it to be something like this:

    Sub Test()



    Dim vAddresses As Variant

    Dim vRef As Variant



    vAddresses = Array( _

        Array("Q38", "AB33"), _

        Array("P40", "AB23"))



For Each vRef In vAddresses

        Worksheets("Input").Range(vRef(1)) = Worksheets("Output").Range(vRef(0))

    Next vRef



Sheets("Output").Cells(40, 17).Value = Sheets("Output").Cells(40, 17).Value



 vAddresses = Array( _

        Array("Q38", "AB33"), _

        Array("P41", "AB23"))

For Each vRef In vAddresses

        Worksheets("Input").Range(vRef(1)) = Worksheets("Output").Range(vRef(0))

    Next vRef



Sheets("Output").Cells(41, 17).Value = Sheets("Output").Cells(41, 17).Value

Does this makes sense?

So repeating it 10x down in rows, and then move 1 column left and do it again.

Hope you get my point, and thanks :)

/Valdemar