CODEWITHSUNDEEP
vbaexcel
user3503230
user3503230

Reputation: 11

Repeat macro in excel

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

Upvotes: 1

Views: 148

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

Upvotes: 2

Related Questions