Macro creates empty rows

[DSLA]

Hello everyone.

In one file we have a macro that collects data from a datafile(other excel) according to the customer number(s).

Here is the code: (the code runs through several fields and gets the sales data according to the customer number in the cell (Range. "D16") and collects it to the CUSTOMER_DATA-sheet)

Dim varFileName1 As String
varFileName1 = Application.ActiveWorkbook.Name
Workbooks.Open Filename:="C:\DATAFILE.XLS"
Dim WB1 As Workbook
Set WB1 = ActiveWorkbook
With WB1.Sheets("DATASHEET")
With .Range("A1:Z65500")
Windows(varFileName1).Activate
.AutoFilter Field:=1, Criteria1:=Sheets("INFOCARD").Range("D16")
.Copy Sheets("CUSTOMER_DATA").Range("A1")
.AutoFilter
End With
End With
WB1.Close savechanges:=False
End If


The problem is that now the code collect the data, but for some reason created 32 000 empty cells also (that is the amount of rows that the DATAFILE.XLS has rows). And the filesize goes up from under 200kb to 2500kb.

Before the code just got the values it found on the search, but now it creates also the empty cell.

Can anyone help me with this one? Please ask if something wasn't clear.

Thanx in advance.

 

[DSLA]

Any helps?

 

[wigi]

Any helps?

I cannot download the first file, so than I'm stuck to help you out.

 

[DSLA]

Both links work, just select Save target as, if the straight clicking of the link doesn't work.

 

[John Davis]

The code you suggested works out great in the future, but now i have about 1500 workbooks that have the macro in them, so changing that would be difficult.

Any more suggestions?

Perhaps you could modify or replace the code in the 1500 workbooks, if necessary. Maybe this will help?

http://www.cpearson.com/excel/vbe.aspx

 

[DSLA]

Perhaps you could modify or replace the code in the 1500 workbooks, if necessary. Maybe this will help?

http://www.cpearson.com/excel/vbe.aspx

Sure this would be possible, but it is quite a project and a half(takes a lot of time) to change them all.

 

[John Davis]

Sure this would be possible, but it is quite a project and a half(takes a lot of time) to change them all.

Either way you go, it's going to take a lot of time. I was thinking along the lines of maybe if all the workbooks were in one folder then looping through each and deleting the module with the macro in it and adding the revised/edited one. However, I'm curious is there a reason you have the same macro in 1500 workbooks? Couldn't you just create one workbook with the macro and then run it on the others?

 

[DSLA]

Either way you go, it's going to take a lot of time. I was thinking along the lines of maybe if all the workbooks were in one folder then looping through each and deleting the module with the macro in it and adding the revised/edited one. However, I'm curious is there a reason you have the same macro in 1500 workbooks? Couldn't you just create one workbook with the macro and then run it on the others?

These are old files and they have been the same for a while, we don't want to update them because of the work it requires, but propably we have to now.

 

[DSLA]

Does anyone have a resolution for this problem?

 

[DSLA]

Anyone?