logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

[VBA] - Macro to Copy Data from External File (wsad.xlsx) into Current Workbook's Sheet 2

tefel 13023 9
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
| New topic
  • #1 13876738
    tefel
    Level 2  
    Hello,

    Due to little knowledge, I wrote this code by analyzing the macro recorded earlier. The (probably trivial) error I can't deal with is that I am not copying data from the batch file, but the macro is executing on the file I run it from. Probably something needs to be added after the command to open the file, but although my head and three do not know what; (

    Code: text Expand Select all Copy to clipboard
    Private Sub CommandButton1_Click()
    Workbooks.Open Filename:="C:\Users\mm\Desktop\Nowy_folder\wsad.xlsx"
           Range("A:A").Copy
           ThisWorkbook.Activate
           Sheets("Arkusz2").Activate
                Range("B1").Select
           ActiveSheet.Paste
    Workbooks("wsad.xlsx").Close
End Sub
  • ADVERTISEMENT
  • #3 13877019
    JRV
    VBA, Excel specialist  
    PRL ThisWorkbook.Activate = Windows (Booklet) .Activate

    Make a complete link to the sheet.
    ActiveWorkbook.Sheets (1) .Range ("A: A"). Copy
    Perhaps, as the workbook opens, it is copied from the current range.
  • ADVERTISEMENT
  • #5 13889568
    tefel
    Level 2  
    Great, works fine ;-) I still have the nature of the "aesthetic" issue. Namely, when copying, a message pops up about a large amount of information in the clipboard, can it be turned off or stuck somewhere so that it selects the "no" option?
  • ADVERTISEMENT
  • #7 13894880
    tefel
    Level 2  
    I may need to copy the B column. The only solution I came up with is below. Is there a more optimal way to prevent the code from taking too long if I add more columns in the future? :D

    Code: text
    Log in, to see the code
  • #9 13895021
    Maciej Gonet
    VBA, Excel specialist  
    Good morning,
    If the data to be copied begins with the first line, the fragment concerning copying can be abbreviated as follows:
    Code: text
    Log in, to see the code


    Regards.
  • ADVERTISEMENT
  • #10 13929126
    seba78787878
    Level 9  
    Sorry that I turn on, and if we do not know the name of the file and try to find it, open it and copy the same
    Code: text
    Log in, to see the code


    enough?
| New topic
Report a violation of the law

Topic summary

The discussion revolves around a VBA macro designed to copy data from an external Excel file (wsad.xlsx) into a specific sheet (Arkusz2) of the current workbook. The initial code provided by the user had issues with activating the correct workbook and copying data. Several responses offered solutions, including ensuring the correct workbook is activated before copying data, using the `Application.CutCopyMode = False` command to suppress clipboard messages, and optimizing the code to copy multiple columns efficiently. Suggestions included using the `With` statement for cleaner code and the `Intersect` method to copy specific ranges. Additionally, a method to open a file dialog for selecting the file dynamically was proposed.
Summary generated by the language model.
ADVERTISEMENT