• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA data collection [SOLVED]

V

Villalobos

Active Member
Hello Forumers,


I have a question regarding the data collection.

I would like to collect the data from the first excel file in to the second file based on various criteria. Could somebody offer a simply code for that?


Example


File #1:

Cell A1 a 0

Cell A2 b 1

Cell A3 c 0

Cell A4 d 2

Cell A5 e 2


If "Cell A1 : Cell A5" > 0 then the code show this in the other file:


File #2

Cell A1 b 1

Cell A2 d 2

Cell A3 e 2


Do you have any idea?


Thanks in advance!
 
Oh, I was not so unequivocal, the name "a" and the value "0" (in the first file) are in two different cells.


And the result will be (in the second file) in two different cells also.
 
Hi ,


You have mentioned VBA in your topic title ; what you want can be done using formulae ; is this acceptable ?


Narayan
 
Hi ,


Suppose I assume that the two files are temp1.xlsx and temp2.xlsx.


Suppose the first file temp1.xlsx has a sheet named Sheet1 , in which the following data is entered in the range A1:B5

[pre]
Code: 
a	0
b	1
c	0
d	2
e	2
[/pre]
Suppose the second file temp2.xlsx has a sheet named Sheet2 , in which the following formula is entered in A1 :


=INDEX([temp1.xlsx]Sheet1!$A$1:$A$5,SMALL(IF([temp1.xlsx]Sheet1!$B$1:$B$5<>0,ROW([temp1.xlsx]Sheet1!$B$1:$B$5)-MIN(ROW([temp1.xlsx]Sheet1!$B$1:$B$5))+1),ROW(A1)))


The above formula is entered as an array formula , using CTRL SHIFT ENTER.


In B1 , enter the following formula :


=INDEX([temp1.xlsx]Sheet1!$B$1:$B$5,MATCH(A1,[temp1.xlsx]Sheet1!$A$1:$A$5,0))


If you wish you can download the respective files from here :


https://www.dropbox.com/s/077y299mxjue064/temp1.xlsx


https://www.dropbox.com/s/figujkt8ecbimxt/temp2.xlsx


Narayan
 
Hi ,


Try this :

[pre]
Code: 
Public Sub Copy_Data_If_Not_Zero()
'          This macro is in one workbook , say Temp1.xlsm
'          It copies data from Sheet1 in this workbook
'          to Sheet2 in another workbook Temp2.xlsm
'          It is assumed that Temp2.xlsm is already open
'          It does not save Temp2.xlsm after copying the filtered data

Dim From_Sheet As Worksheet, To_Sheet As Worksheet

Set From_Sheet = ThisWorkbook.Worksheets("Sheet1")
Set To_Sheet = Workbooks("Temp2.xlsm").Worksheets("Sheet2")

From_Sheet.Activate
With From_Sheet
.AutoFilterMode = False
.Cells(1, 1).AutoFilter field:=2, Criteria1:=">=1"
.AutoFilter.Range.Copy To_Sheet.Cells(1, 1)

.AutoFilterMode = False
End With
End Sub
The data in Temp1.xlsm , on Sheet1 , is as follows :

[pre][code]Names	Values
a	0
b	1
c	0
d	2
e	2
[/pre]
After the filtered copy , the data in Temp2.xlsm , on Sheet2 , is as follows :

Names Values
b 1
d 2
e 2[/code][/pre]
Narayan
 
Hello Narayan,


I have one more question.

How do you do with vba that if:


in Temp1.xlsm

The "Names"=columnA and the "Values"=columnD (between column "A" and "D" many data).


and in Temp2.xlsm

I would like that the "Names" and "Values" will be in columnA and columnB.


I tried to find something, but I had not luck.

Do you have any idea?
 
Hi ,


Try this :

[pre]
Code: 
Public Sub Copy_Data_If_Not_Zero()
'          This macro is in one workbook , say Temp1.xlsm
'          It copies data from Sheet1 in this workbook
'          to Sheet2 in another workbook Temp2.xlsm
'          It is assumed that Temp2.xlsm is already open
'          It does not save Temp2.xlsm after copying the filtered data

Dim From_Sheet As Worksheet, To_Sheet As Worksheet

Set From_Sheet = ThisWorkbook.Worksheets("Sheet1")
Set To_Sheet = Workbooks("Temp2.xlsm").Worksheets("Sheet2")

From_Sheet.Activate
With From_Sheet
.AutoFilterMode = False
.Cells(1, 1).AutoFilter field:=4, Criteria1:=">=1"
.AutoFilter.Range.Resize(, 1).Copy To_Sheet.Cells(1, 1)
.AutoFilter.Range.Offset(, 3).Resize(, 1).Copy To_Sheet.Cells(1, 2)
.AutoFilterMode = False
End With
End Sub
[/pre]
Narayan
 
You must log in or register to reply here.
Back
Top