Copying data in VBA from different named workbook each time

  • [SIZE=12px]The code below does what I want but is it possible to activate the same range from a different named source workbook each time?


    Ideally I didn't want any code in the source file.


    I am doing this for a friend who has next to no knowledge at all with Excel. I know if she saves each order with the same name each time this will work from the destination workbook as below.


    Any ideas would be appreciated. Thanks very much




    Sub RetOrdfrmdata()
    '
    '
    Application.ScreenUpdating = False

    ' Windows("LOFtestvar.xlsx").Activate - SOURCE WORKBOOK
    Range("C74:C118").Select
    Selection.Copy

    Windows("LJA databasetestvar.xlsm").Activate - DESTINATION WORKBOOK

    Range("C54").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("a3").Select

    End Sub
    [/SIZE]

  • Hello,


    You could test the following macro :



    Please note both workbooks : Source and Destination must be opened ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim
    Thanks for the very speedy response.


    I can see this would work but I am trying to avoid putting any code in the source workbook. This is an orderform going out to her customers, didn't want coded workbooks going to them.
    I would like to start with destination as the active workbook - If I set destination as wkb1 is there some way of setting source as wkb2?


    I understand both workbooks must be opened.


    Once again many thanks - Steve

  • Hi again,


    Sorry read your initial message way too quickly ...:-(


    Below is a proposal which should be in line with your objective ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • ....................' Adjust the Copy Instruction to the specific Sheet Names and Ranges
    ' Workbooks(Sourfile).Sheets("LJA").Range("C74:C118").Value = Workbooks(Mainfile).Sheets("LJA").Range("C54:C98").Value



    Workbooks(Sourfile).Activate
    Workbooks(Sourfile).Sheets("LJA").Range("C74:C118").Select
    Selection.Copy

    Windows("LJA databasetestvar.xlsm").Activate

    Range("C54").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("a3").Select

    End sub()




    Hi Carim
    I don't know how to copy code so it highlights in grey but where you noted to adjust copy range to suit - the above worked better for me.
    Otherwise really pleased with this and exactly what I wanted.
    Once again many thanks.
    Steve

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!