Trying to copy range of values from one WB to another by finding date in list

[robxl]

I've got a mess here that I'm struggling through. I am trying to copy WB(Book1) Sheet(Net) Range(E7:E9) to WB(Book2) Sheet(Sheet1) corresponding col's C,D, E based on matching date from Book1 to Book2 range of dates col B (1/1(B4) to 12/31(B369)). So very stuck. Any help greatly apreciated! What I have so far:

Please Login or Register  to view this content.

[Marc L]

So without code tags (so easy via the icon !) according to the forum rules - a must read ! -
I just can advise to use the easy fliter or advanced filter …

[robxl]

Thx Marc, will do the code tags, thought that was the commenting I did in code!

[Marc L]

Thanks ! Now helpers can take a glance to your need …

[Marc L]

According to your attachmment a VBA beginner starter demonstration where
you must update the file path variable if Book2 is not in the same folder than Book1 :

Sub Demo1() 
    Dim F$, Rg As Range 
        F = ThisWorkbook.Path & Application.PathSeparator & "Book2.xlsm" 
        If Dir(F) = "" Then Beep: Exit Sub 
        Application.ScreenUpdating = False 
    With Workbooks.Open(F).ActiveSheet 
        Set Rg = .Range("B3", .[B3].End(xlDown)).Find(Sheet2.[B4].Value, , xlFormulas) 
        If Not Rg Is Nothing Then Rg.Columns("B:D") = Sheet2.[TRANSPOSE(E7:E9)] 
       .Parent.Close Not Rg Is Nothing 
    End With 
        Application.ScreenUpdating = True 
        If Rg Is Nothing Then MsgBox "Date not found", vbExclamation, "Copy" Else Set Rg = Nothing 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[robxl]

Man that's great, it works! Thanks a million Marc!

[Marc L]

Read the advise under the code - as the way to thanks helpers on forum - and

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[robxl]

Marc, I hate to bother you again but I just had a chance today to incorporate the code into my project. The issue is the samples I provided unfortunately aren't quite the same. My sample was just numbers I entered. My actual sheets have formulas I input to get those values. So for some hours today I've been trying to get to the bottom of the prob. Simply transposing from the cells with formulas doesn't yield a value (returns "0"). I've tried several approaches with the code. I have the latest posted below. I tried defining an array and values in a range. That returned a #name error. I directly tried to transpose the cells and get the #value error. I couldn't manual transpose and get values other than "0" so I tried copy and paste special and got the values. I recorded a macro and looked at the code for this as a way to transpose the values but hit another dead end. Any insight and help is always greatly appreciated! Also saw your note and added rep.

Please Login or Register  to view this content.

[robxl]

Well I woke up this morning and reopened the project. I tried the code where I used Paste Special and it seems to work... (code below). I guess my question would now be, is this the best/most efficient method?

Many thanks!

Please Login or Register  to view this content.

[Marc L]

Posting an attachment with a different layout than the original is often not a good idea
or you must be very confident with your Excel / VBA skills to amend any code …

According to my demonstration and your last attachment :

Sub copytothroughputsMOD() 
    Dim F$, Rg As Range 
        F = ThisWorkbook.Path & Application.PathSeparator & "Throughputs.xlsm" 
        If Dir(F) = "" Then Beep: Exit Sub 
        Application.ScreenUpdating = False 
    With Workbooks.Open(F).Sheets(1) 
           Set Rg = .Range("B3", .[B3].End(xlDown)).Find(Sheet11.[C2].Value, , xlFormulas) 
        If Not Rg Is Nothing Then Rg.Columns("B:K") = Application.Index(Sheet11.Columns(15), [{13,16,19,22,24,27,31,33,35,37}]) 
       .Parent.Close Not Rg Is Nothing 
    End With 
        Application.ScreenUpdating = True 
        If Rg Is Nothing Then MsgBox "Date not found", vbExclamation, "Copy" Else Set Rg = Nothing 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[robxl]

I wouldn't say I'm that confident in VBA but I have had success in the past with amending code and if nothing else using the debugger. I either try to find examples or record macros and read through to see what is going on in the code. I greatly appreciate your help and your coding is MUCH better than a lot of examples I've seen (in general). I will use the code you've attached. I've learned a lot! Thanks again!

[Marc L]

Thanks for your appreciation !

In last code, Application.Index is the worksheet function INDEX as Excel features can often help to write a code easier …

Using the Macro Recorder is a good way to see which statements are needed for a particular task
but after you have to clean the code removing the useless like for example the Select, Activate, …
… as playing directly with objects is often more efficient.