Edit looping code to copy range from one workbook to another

[mrbusto71]

Heya!

Please Login or Register  to view this content.

So I had a thread on this yesterday and I didn't want to dredge it up again, so I'm posting a new thread with the code I've compiled thus far.

Attached is an example workbook. Sheet1 and Sheet2 on this workbook would be separate workbooks in practice, but are just sheets for consolidation purposes.

Basically, what I'm trying to do is have Excel use the value in Sheet1, A2 as the lookup option, copy the specified range, then find the value on Sheet 2 and paste it. Then I'd like it to move on to the next name on Sheet 1 which is in A18, and repeat the exact same process. Then do that over and over until it hits a blank, then stop.

The concept of looping makes sense, but the declaration of i or x as Long is wildly confusing to me, so I'm not sure how to tell the code to execute what I have above starting at A2 then A18, then A34, etc etc.

I spent a majority of yesterday evening trying to google it, but this is the best I've come up with so far and it isn't working. If anyone could identify the flaw in my code/logic and help me out, I'd appreciate it!

[LeoTaxi]

If step is always 16 between 2 names this can do

Please Login or Register  to view this content.

Kind regards
Leo

[mrbusto71]

If step is always 16 between 2 names this can do

Please Login or Register  to view this content.

Kind regards
Leo

Awesome! I tried your code and I'm getting an error. When I step through the code with F8, I make it through the Workbooks.Open event and then it throws me a

Run-time error '438':

Object doesn't support this property or method

error. The Workbook opens, but then the code stops and that error is what I get.

Here's my code:

Please Login or Register  to view this content.

I noticed mypath = had a Workbooks.Open event and wb2 did also, so I removed the one from mypath and just declared it as the path instead but that resulted in a Object Not Defined error.

Any idea where the errors are? I'm going through trial and error currently but not with much luck.

[LeoTaxi]

try first to change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and let me know

Kind regards
Leo

[mrbusto71]

Heya! Tried that but now I'm getting a Run-Time error '438': Object does not support this property or method.

It goes through everything smoothly but once it gets to the part where it needs to open the workbook, it opens it then stops and that error comes up

Not sure what I'm missing.

[LeoTaxi]

i think something wrong with the sheet name you create with variables

what is the sheet name in workbook you open ?

[mrbusto71]

i think something wrong with the sheet name you create with variables

what is the sheet name in workbook you open ?

I did a debug.print mypath to see if it was doing the right thing, which it is (in the sense that it's pulling up the correct item, not that it's not flawed in some way).

The path to the workbook is S:\2016\10_October_2016\Productivity 10.10.16.xlsx

The reason I set these as variables instead of a static line is because this entire process is to accommodate a weekly transfer I need to do. So rather than typing in the year, then the format of date/month/year, etc all the time, I have it referencing B1 of workbook 1 using

Please Login or Register  to view this content.

and then pulling the year/month/date from that value.

I'm not sure why it successfully names and opens the correct workbook then errors out though? Do I need to set the wb2 variable later or something?

If I can get this last part to stop breaking, I think the code you put together will execute everything else flawlessly (I tested it having it transfer from Sheet1 to Sheet2 in a single workbook and it worked great, but now that I'm specifying an outside workbook it's going sideways).

I appreciate your help with this.

[LeoTaxi]

just test with this code

Please Login or Register  to view this content.

without any problems

so if the error comes after opening the workbook i would check the names of the worksheets, are both correct in code ?

Kind regards
Leo