Problem in Copying data from one workbook to another using VBA

[Saraah]

Thanks vletm.

I have a query regarding the first problem where master imports from Databook.
I have data in column L in Master workbook which contains a formula.
Now when I try to import from Databook, the macro is not working properly. Instead it is moving column L data to column A.
Can you please help me in this. What changes are required for this purpose in macro.
Please note that formula in column L should remain as such so that when data gets imported from Databook , it can calculate the numbers.

Thanks.

 

[vletm]

@Saraah I'm checking the code.
Now, it can clear 'Master' A:K data.
It'll copy all possible columns from 'Databook' ( 'ROB' rows ).
Databook's A-column has formula.
A-columns value will copy to 'Master's A-column; same with L-columns values.
Is 'New Master's ' column-L always like =K2*2. If so then no need formula.
Can You send 'new Databook'-file? I can see 'the challenge'.

 

[Saraah]

@vletm The Databook remains the same as before. It does not have anything in column L.
The only change is that master workbook now has formula in column L that I send you before and I want it to always have formula as it is so that when data is imported from Databook in column A to K , it can calculate the double of it as in (K2*2).
We cannot make any change in Databook and want the formula in Master only.
The only change is if any other column apart from A:K has formula , it should remain as such ( example now we have in column L). Running of the macro should not impact column L as it is doing now.

 

[vletm]

@Saraah . Okay! I see, Databook's column L is empty.
Here's the next version.
There are few things to notice.
If Databook's result has always same amount rows, then You can keep original formulas in 'column L'.
If 'column L' has always same formula, like in row 2 '=K2*2', then safer is that You got the result of that formula to 'column L', like '-60000'.
This's my opinion.
Now, You have there two versions.
If cell's [L1] text is bolded ( Rate )then You will get results to 'column L' else
is not bolded ( Rate ) You'll get formulas to 'column L'.
It's Your choice.

 

[Saraah]

@vletm I see the code you have used above is particularly for formula (K2*2).
There are possibilities that we might change the formula. This formula can be changed to get our desired calculations. Also it is possible to have certain complicated formulas from column "L" onwards. So I want that column L onwards should remain untouched while importing the data. Also we cannot input a certain condition for a particular formula in column L because we can change it anytime.
So I want to import the data as such as it was before but with a liberation to have anything column L onwards without impacting macro.
Please advise what are the possible solutions.

Thanks.

 

[vletm]

@Saraah Modified:
Macro don't change column L,
but if there are different numbers of used cells (compare Columns K and L) the user will get error message.

 

[Saraah]

@vletm I did not understand your point. Can you please elaborate.

 

[vletm]

I'll try.
The newest version of Macro do not modify Column L at all.
Now, 'Master' has 34 rows (header and 33 data).
Now, Column L, there are fixed formulas from row 2 to 33 (I deleted 34. row's formula).
If You import data, which has ex. 40 rows.
The data starts from row 2 and ends to row 41 [A2:K41].
Column L, there are still fixed formulas [L2:L33]; nothing [L34:L41]!
You gotta manually add 'missing' formulas.
If not, You could miss those values.
If You import data, which has ex. 20 rows.
This case can cause something.
It all depends, what are You going to do with Column L -values.
If You count, how many filled cells are in Column L .. or what ever.
You could get wrong information.
I've seen many times ( not in this case ), that there are mistakes (different formulas) because those had made manually.
That message gives information that 'something is missing in Column L'.

 

[Saraah]

@vletm Thanks that's a great solution.
However when I am trying this macro on original file I am facing another issue.
The file Databook which I am trying to import is originally in CSV format. So the sheet automatically takes the name of the file which is "Databook_dd/mm/yyyy".
dd/mm/yyyy=current date
As per the macro above the sheet name should be same as the sheet in master workbook but due to databook sheet name getting changed everyday , the macro does not work.
Can you please provide me a solution for this ?

Thanks.

 

[vletm]

@Saraah Okay...
I noticed that point ... it was one check point.
So, from this moment the data has to be in 1st sheet of data-file; no matter name of sheet.
How about now?

 

[Saraah]

@vletm The problem remains the same. If the name of the sheet in Master and Databook is same the macro works. But due to sheet name of Databook is changing on daily basis and I cant change for the Master, its not working.
The macro doesnot work when I take different names for sheet in Master and Databook.
What should be done in this case?

 

[vletm]

@Saraah
Did You test with the newest 'Master.xlsm'?
Is that data in the 1st sheet in Databook?
Can You send one data-file for me?

 

[Saraah]

@vletm Yes I tried the one you sent. And the data is in first sheet only.
I am attaching Databook.

 

[vletm]

@Saraah
There were one wrong letter in the code (Ln52, Col52 'a' => 'b').
Tested better, now it works.

 

[Saraah]

@vletm The file loaded above by you works. But when I try same formula in my another workbook it doesnot work.
Can you please look into this what is the problem. Its the same formula and almost same data. The macro doesnot show any error but data doesnot get imported. The file again works for xlsb. but when I try csv its not working.
The similar I faced above but i have used the same code used by you.
I have attached the two files:Master and Data.

 

[vletm]

@Saraah ... hmm...
I tested with those files ... works as fine as possible.
There are different numbers of columns! But, it works...

Anyway ... I added few lines for Your testing.
'Master Book2.xlsm' cell [AA1] is 'the trigger'.
If cell [AA1] is NOT EMPTY then You'll see many message boxes (as many as rows). The last line of that Message tells 'to copy' or not!
If ALL FALSE then no copied values!

If cell [AA1] is empty then no Messages!

2nd thing ... have You tested this with Your previous 'data-files'?
If now only 'data-file' is different then 'dates'!
I tested and it works.

3rd thing ... You would test to verify that both files date-columns are in same format. Sometimes ... sometimes ... dates could make this.

Ideas ... questions?

 

[vletm]

@Saraah - This still works ...
You could test next too.
1) Empty that [AA1] ( => No messages! )
2) Add those three 'Expressions' to 'Watch' and One BreakPoint, just like in photo.
3) Run that Macro.
4) Check value-column! Those should be like in photo; the 2nd row!
If 2nd expression is like "28/10/2015", there will be a challenge.
Test and report.

 

[Saraah]

@vletm Sorry for the late reply.
But this is not working for me. The previous files were working fine for me. The only problem is when I try the same formula with another file it doesnot work.
I am unable to understand if that can work with one file, why not with other.
However the xlsb. file is getting imported but there is an issue with the csv. one. The macro empties the data , it displays "ready" message too but the data is not copied into the master.
The file you sent me also did not work and I even did not understand why you were making those changes when the formula was working perfectly for our previous files.
Please help.

Saraah

 

[vletm]

@Saraah
That the newest file has some features that You could test the data-file.
If You empty that cell [AA1] then it works as normal as previous versions.
Did You make that test?
Did it always gave the false result?
It's challenge to help if You cannot help to find it!
"Ready" comes then the Macro has tested all rows.

I need data file which don't work ( .cvs or any)!

 

[Saraah]

@vletm i tested the file but again it worked for xlsb not for csv. When i try to import a csv format file data does not get imported. I am unable to upload a csv file here. The extension is not allowed here. If you simply convert the xlsb file i sent you to csv then you can probably see what is the problem. I need to import csv file only . And i m also wondering if its says ready then why it doesnt copy the data.

 

[Marc L]

Saraah, rename an original textfile.csv as textfile.csv.txt and upload it here.
​

 

[Saraah]

I tried to upload txt file below. Let me know if you are able to access this file to figure out the problem.

 

[Marc L]

It is not a csv text format !
A csv file has a separator between data on each line (common is comma) …
This is a tabulation separator text file format and
no problem to open it whatever any Excel version …​

if you are able to access this file to figure out the problem.

Which problem ?
​

 

[Saraah]

The problem is that the macro is not working for csv file. And i m unable to upload it here so problem can be seen. But still i cant upload csv file here . Csv.text is also not allowed here.

 

[vletm]

Date column is 'string' in that file ...
Just now missing 'maxdate' of those dates..