Repeat macro in excel

Question

I'm incredibly new to VBA for Excel, and ashamed to admit that I don't understand what I've read so far! I'm trying to copy and paste from one sheet to another, and then recopying the results from the second sheet back into the first sheet. I have about 5300 items that I wish to copy - paste from the original sheet. Here's the recorded macro that resulted:

Sub Retreive_Code()
    Range("L11").Select
    Selection.Copy
    Windows( _
        "Unique License plate Valuation Algorithm.18March2014.V7.0.Winner!.xlsx"). _
        Activate
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Complete Unique number sales.2010 - 2012.xlsx").Activate
    Range("M11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Please let me know if you have any questions at all. Many thanks

`Please let me know if you have any questions at all` - we doesn't have any questions. Do _you_ have any questions to us? — Dmitry Pavliv, Apr 06 '14 at 10:21
I certainly do! I need to know how I can copy this formula across the complete length of the column using VBA. — user3503230, Apr 06 '14 at 10:27
what ranges are you want to copy and where you need to paste them? — Dmitry Pavliv, Apr 06 '14 at 10:29
I need to paste the following range (L12:L5283) and would like to paste the results in (K12:K5283) — user3503230, Apr 06 '14 at 10:33
have you tried to record a macro for copying entire range (L12:L5283), rather than single cell? — Dmitry Pavliv, Apr 06 '14 at 10:34
it the same as for single cell:) just select entire range instead single cell and try to record your macro for copying data — Dmitry Pavliv, Apr 06 '14 at 10:56

score 2 · Answer 1 · edited May 23 '17 at 11:57

2

Please avoid the use of .Select/.Activate INTERESTING READ

Is this what you are trying?

UNTESTED

Sub Retreive_Code()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet

    '~~> Set your workbooks here
    Set wb1 = Workbooks("Unique License plate Valuation Algorithm.18March2014.V7.0.Winner!.xlsx")
    Set wb2 = Workbooks("Complete Unique number sales.2010 - 2012.xlsx")

    '~~> Change these to the respective sheets
    Set ws1 = wb1.Sheets("Sheet1")
    Set ws2 = wb2.Sheets("Sheet1")

    '~~> Do the copy pasting
    With ws2
        ws1.Range("D12:D5283").Value = .Range("L12:L5283").Value

        DoEvents

        .Range("M12:M5283").Value = ws1.Range("L12:L5283").Value
    End With
End Sub

edited May 23 '17 at 11:57

Community

1
1

answered Apr 06 '14 at 10:39

Siddharth Rout

147,039
17
206
250

1

`Range1.Value = Range2.Value` imo is better than copying then pasting for equivalent sized ranges. Otherwise, a clean answer :) – Cor_Blimey Apr 06 '14 at 10:42
True @Cor_Blimey. Updating that :) – Siddharth Rout Apr 06 '14 at 10:42

Repeat macro in excel

1 Answers1