0

I just started learning vba/macro to simplify some task.

The code below works fine but I am wondering if it can be simplified by adding some For loops or editing some parts.

Problem:
To copy from workbook 1 then paste in to workbook 2. There are irregularitie. For example, Workbook1-Column3 C will paste into W2C5, then W1C6 into W2C6. Etc.

this is my current macro/vba code

Sub copypaste_openPO()

    Sheets("Open PO").Select
    Windows("Open Purchase Orders.xlsx").Activate
    Range("E5:F9999").Select
    Selection.Copy
    Windows("Action Tool.xlsm").Activate
    Range("B2").Select
    ActiveSheet.Paste

    Windows("Open Purchase Orders.xlsx").Activate
    Application.CutCopyMode = False
    Range("H5:P9999").Select
    Selection.Copy
    Windows("Action Tool.xlsm").Activate
    Range("D2").Select
    ActiveSheet.Paste

This goes on for about 20+ times. and it quite inefficient.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
mylittlepony
  • 1
  • Read this about using Select https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Alexey C Jan 30 '19 at 07:47
  • You can only do it in a loop if you can make your code find the source range and determine where the destination range will be. We cannot tell you **how** exactly to do that because it highly depends on how your data looks like. So think about how *you* manually decide which data needs to be copied (how do you find it as a human) and then try to use these criterias to write your code to determine this range. – Pᴇʜ Jan 30 '19 at 07:52
  • Ok thank you! I figured out its simpler to make my datasets look alike or similar then continue doing my copy pasting – mylittlepony Jan 31 '19 at 12:42

0 Answers0