Problem in Copying data from one workbook to another using VBA

[Saraah]

Hi All,

I have a master workbook where I want to copy data from another workbook named Databook. I want to copy this using VBA code in Master workbook based on a criteria that column C contains "ROB" in it.
I have written a code for it but it is picking only the first row where column "C" contains "ROB" whereas I want to copy all of the data based on "ROB" from Databook.

Please note that data in Databook can increase or decrease.
Also I always want to paste data in Row 2 of Master workbook.

Can somebody help me in this ?

 

[vletm]

Your code closes ActiveWorkbook after it finds the 1st "ROB".
You should keep 'Databook' open until all rows has checked.
You can edit it!

 

[Saraah]

Can you please suggest me changes in code ?

 

[Marc L]

Hi !

Easy way is to use an advanced filter if source workbook is opened
(less than ten codelines) …
You can directly use it from destination worksheet and,
just by activating Macro recorder, you will get a base of code !

See also AdvancedFilter method in VBA inner help …

With a closed workbook, you can import data
via ADODB ActiveX and a bit of SQL code …
​

 

[vletm]

@Saraah test this sample,
click [Import]-button

 

[Marc L]

Using an advanced filter simplifies the code and speeds up its execution !

The next demonstration (Databook in same path than Master) needs only
two codelines to set an advanced filter and copy filtered data !
See codelines #9 & 10, paste code to Master :​

Sub Demo()
            Dim Wb As Workbook
            Application.ScreenUpdating = False
            Set Wb = GetObject(ThisWorkbook.Path & "\Databook.xlsx")
With Sheet1
    With .UsedRange.Rows
      If .Count > 1 Then .Item("2:" & .Count).Clear
    End With
         .[C2].Value = "ROB"
    Wb.Worksheets(1).Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[C1:C2], .[A1:K1]
End With
                Wb.Close False
            Set Wb = Nothing
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[vletm]

@Marc L, I tried to learn from Your code ...
Why do it moves 107 rows, all rows? not only "ROB" rows?
Did I miss something?

 

[Marc L]

Well works on my side ! As you can see here :​

Maybe it's your Excel version or any mod you have done …

Try with Code & ROB in cells N1:N2 of Sheet1 of Master workbook
and update line #10 for criteria instead of C1:C2 …​

 

[Marc L]

Instead of using Master for criteria, let's try with Databook
then my demonstration needs « less than ten codelines » !

Paste code to Master workbook :​

Sub Demo()
With GetObject(ThisWorkbook.Path & "\Databook.xlsx").Worksheets(1)
    .[N1:N2].Value = [{"Code";"ROB"}]
    .UsedRange.AdvancedFilter xlFilterCopy, .[N1:N2], Sheet1.[A1:K1]
    .Parent.Close False
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

 

[vletm]

Okay! Your 1st code works with Your version!
I did only one mod, .Path & "\Data to .Path & ":" & "Data
The code row .[C2].Value = "ROB" works fine, [C2] gets value ROB.
Ops! Your second version of code comes...
It seems to work Okay, only "ROB"s, YES.
Gotta learn later more, thanks.

 

[Marc L]

I tried my first demonstration with different Excel versions …


​

 

[vletm]

Okay! missing 's' ... Your 1st code works with Your versions!
and still gotta learn later more, thanks.

 

[Saraah]

Hi Vletm and Marc L,

I was able to run the code sent by vletm. However i didnot understand the advanced filter concept above.
Also i have another issue with this. The master file here has a date attached to it which changes everyday (for eg- Master_mmddyyyy) which means i cannot fix its name as WB_1 as mentioned by vletm in his code.
Can you please suggest what can be done in this case. Due to this problem the code is not working in the original file.

Thanks

 

[Marc L]

Advanced Filter is an Excel basics : functionality existing since
around 20 years to easily filter & copy data directly from worksheet
without the need of a code ! You just have to train in Excel,
10/11 years old kid succeed to use it, so it's easy !
See Excel help and tutorials over the Web …

Via VBA avoiding a slower & more complicated gas factory code …
​

 

[vletm]

@Saraah, check this ready to use file
Default Master file is automatic and no matter 'Sheet1'-name too.
You only have to copy, rename and 'click'.

 

[Saraah]

Hi vletm,

The file works. Thanks a lot.
However I have one more question. if I want to import a file which changes on daily basis. For example: "Databook_11112015" is the file to be imported and the there is separate file for everyday with latest date in its name. How can we set the code so that it picks latest date file everyday?

 

[Debaser]

Marc's original code will not work as posted because it doesn't include the column header in C1. I imagine it works for Marc because he put it in manually and the code doesn't clear row 1.

 

[Marc L]

Wrong : my demonstrations work with original joined workbooks
from post #1 like showed in animated screen of post #8
with no manual mod ! It's better to try before writting anything …

Saraah, how the daily file is named with days under 10 ?
Leading zero ? Any sample ?

It's easier to pick up a daily file using GetOpenFilename method …
​

 

[Debaser]

I apologise. I had not looked at the Master workbook originally supplied, which does indeed have the headers already in place.

 

[Marc L]

No problemo !

Saraah, tell me your choice, if GetOpenFilename method suits your need …

​

 

[vletm]

@Saraah Test this ...
You can select Your file; it have to have sheet named 'Sheet1'.
It will pick only the latest dates data.

 

[Saraah]

Thanks vletm. The file works well. You guys are of great help.

Marc L I am not sure about Getopenfilename method, however would like to know more about it.

Thanks.

 

[Saraah]

Hi ,
I have problem in writing another code.
I have a workbook called "Entry book" and another called "Product details".
I want to import data into Product details for Item code, Model, Price and Interest from Entry book based on a date specified in Product details.

Please note that this data has to be imported into a specific cell corresponding to its name. Also the date in Product details changes everyday and the data in Entry book is roll forward everyday. So I want to pick data only of a particular date from Entry book.
Please help me in writing this code. I am unable to understand how should I go about this.

Thanks.

 

[vletm]

Write wanted date to cell [C1] and press [Import numbers]
for testing...

 

[vletm]

ver2 ..
Select wanted date from cell[C1] and press [Import numbers]
for testing ...