Transfer of data divided into chunks from one file to multiple files.

[dilshod_k]

Hello everyone,

I have multiple destination workbooks each one with a list of 100 symbols (one symbol per cell) in column A starting from cell A25.
I have also file "Source.csv" with a list of 2000+ symbols in column B starting from B2.
To copy and paste all 2000+ symbols in the column of a single workbook is not an option, this list needs to be divided into 100 symbols chunks and copied to destination workbooks one chunk per workbook.
The aim is to write code which would delete old symbols in destination workbooks (lets say A25 to A125) and replaced them by 100 symbols chunks (plus whatever remains after total number of symbols divided by 100 to the last destination workbook) taken from the file "Source.cvs". At the beginning and by the end of the code all files should be closed. The code should be running from the file called "Master.xlsm".
I would be grateful for any suggestions especially those that are not using Copy-Paste method in order to improve performance of the code. Thanks in advance.

 

[DanteAmor]

I have a couple of doubts:
If you have 2000+ symbols then you need more than 20 books in the folder and if you have less than 20 books?
The first 100 symbols of the 2000+ symbols in which book should be copied? If it does not matter then try the following code. It does not copy and paste, what it does is pass the values ​​of 100 cells to 100 destination cells.

Your book with the macro and the book source.csv must be in a folder, in another folder put the destination books.

Change in this line the folder name of the destination books.


wPath3 = "C:\trabajo\Books" 'folder with destination workbooks

Sub Transfer_data()
'
    Dim wPath1 As String, wPath3 As String
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Dim ws2 As Worksheet, ws3 As Worksheet
    Dim wFiles As Variant, n As Double
    '
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '
    wPath3 = "C:\trabajo\Books\"    'folder with destination workbooks
    '
    Set wb1 = ThisWorkbook
    wPath1 = wb1.Path & "\"
    Set wb2 = Workbooks.Open(wPath1 & "Source.csv")
    Set ws2 = wb2.Sheets(1)
    '
    wFiles = Dir(wPath3 & "*.xls*")
    n = 2
    Do While wFiles <> ""
        Set wb3 = Workbooks.Open(wPath3 & wFiles)
        Set ws3 = wb3.Sheets(1)
        ws3.Range("A25").Resize(100).Value = ws2.Range("B" & n).Resize(100).Value
        n = n + 100
        wb3.Close True
        wFiles = Dir()
    Loop
    wb2.Close False
    '
    MsgBox "End"
End Sub

 

[dilshod_k]

I have a couple of doubts:
If you have 2000+ symbols then you need more than 20 books in the folder and if you have less than 20 books?
The first 100 symbols of the 2000+ symbols in which book should be copied? If it does not matter then try the following code. It does not copy and paste, what it does is pass the values ​​of 100 cells to 100 destination cells.

Your book with the macro and the book source.csv must be in a folder, in another folder put the destination books.

Change in this line the folder name of the destination books.


wPath3 = "C:\trabajo\Books" 'folder with destination workbooks

Sub Transfer_data()
'
    Dim wPath1 As String, wPath3 As String
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Dim ws2 As Worksheet, ws3 As Worksheet
    Dim wFiles As Variant, n As Double
    '
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '
    wPath3 = "C:\trabajo\Books\"    'folder with destination workbooks
    '
    Set wb1 = ThisWorkbook
    wPath1 = wb1.Path & "\"
    Set wb2 = Workbooks.Open(wPath1 & "Source.csv")
    Set ws2 = wb2.Sheets(1)
    '
    wFiles = Dir(wPath3 & "*.xls*")
    n = 2
    Do While wFiles <> ""
        Set wb3 = Workbooks.Open(wPath3 & wFiles)
        Set ws3 = wb3.Sheets(1)
        ws3.Range("A25").Resize(100).Value = ws2.Range("B" & n).Resize(100).Value
        n = n + 100
        wb3.Close True
        wFiles = Dir()
    Loop
    wb2.Close False
    '
    MsgBox "End"
End Sub

Hi DanteAmor,

Thanks for your help. I tried to run your code with adjustments, but it gives error message: Run-time error ‘91’: Object variable or With block variable not set. Apart from that I was wandering is there any chance to write it the way so that I could keep all files in one directory? May be using something like:
For Each Fl In FlDr.FilesIf Fl.Name <> "DataFile.xlsm" and <> "Source.csv" Then ' Sorry I do not know syntaxis properly as I'm doing first steps in coding.

Please find your code below as it follows with details of error message:

Sub Transfer_data()
'
Dim wPath1 As String
Dim wPath3 As String
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim wFiles As Variant
Dim n As Double
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Your book with the macro and the book source.csv must be in a folder, in another folder put the destination books.
'
' Change in this line the folder name of the destination books.
'
wPath3 = "C:\Users\User\Downloads\Trading\Test\SpeedUp Tuning\New folder" 'folder with destination workbooks
'
Set wb1 = ThisWorkbook
wPath1 = wb1.Path & ""
Set wb2 = Workbooks.Open(wPath1 & "finviz.csv")
Set ws2 = wb2.Sheets(1)
'
wFiles = Dir(wPath3 & "*.xls*")
n = 2
Do While wFiles <> ""
Set wb3 = Workbooks.Open(wPath3 & wFiles)
Set ws3 = wb3.Sheets(1) '================= ERROR MESSAGE HERE===========' Run-time error ‘91’: Object variable or With block variable not set.
ws3.Range("A25").Resize(100).Value = ws2.Range("B" & n).Resize(100).Value
n = n + 100
wb3.Close True
wFiles = Dir()
Loop
wb2.Close False
'
MsgBox "End"
End Sub

 

[DanteAmor]

Why do you remove the backslash?

wPath3 = "C:\Users\User\Downloads\Trading\Test\SpeedUp Tuning\New folder\"

wPath1 = wb1.Path & "\"


---
You could upload a copy of your file "finviz.csv" to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[dilshod_k]

Why do you remove the backslash?

wPath3 = "C:\Users\User\Downloads\Trading\Test\SpeedUp Tuning\New folder\"

wPath1 = wb1.Path & "\"


---
You could upload a copy of your file "finviz.csv" to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Obviously I did not type manually all this code (and I did not remove these backslashes deliberately), I used copy-paste method, although I can't explain absence of the slash in only these two places.
In the version of your code which I tested - both of these backslashes were there and the Error message is still there: Run-time error ‘91’: Object variable or With block variable not set.

Now I tried to run code with line: wPath3 = "C:\Users\User\Downloads\Trading\Test\SpeedUp Tuning\New folder" - it does not give any error message and the code does not change anything in the files.

When I tried to run code without backslash in : wPath1 = wb1.Path & "" - it gives absolutely different error: Run-time error '1004': Sorry, we could not find C:\Users\User\Downloads\Trading\Test\SpeedUp Tuning\New folder\Upload List Of Stocks finviz.csv Is it possible it was moved, renamed or deleted?

 

[DanteAmor]

I know you didn't write the code, but those backslash must exist. Try again with the backslash

If you have an error, I need to see your .csv file to review what the problem is with your sheet1

 

[dilshod_k]

I know you didn't write the code, but those backslash must exist. Try again with the backslash

If you have an error, I need to see your .csv file to review what the problem is with your sheet1

The error is still there even with backslashes.
If you want to have a look at finviz.csv you can find it here:
https://www.dropbox.com/s/flha2xrzf9ca7p3/finviz.csv?dl=0

 

[DanteAmor]

I already tried the macro with your file and it works.


Maybe some of the macro is not being copied correctly.


I attach my file with the macro, just put the file of the macro and your csv file in a folder for example:

""C:\Users\User\Downloads" (whatever it is, but which is different from the destination file folder.)

and the other files in the folder:
"C:\Users\User\Downloads\Trading\Test\SpeedUp Tuning\New folder"

The macro is already updated, do not modify it, just execute the macro.

https://www.dropbox.com/s/rxnesu6k12akn3u/Master.xlsm?dl=0

Try and tell me.

 

[dilshod_k]

Thank you very much. It is working now. My fault is that in the description I forgot to mention that in the destination workbook destination sheet is not the first one but the second one (has name "Control"). As a result it transfers data in the first sheet instead of second. Another glitch is that apart from destination folder it transfers data in any other file in the same directory with Master.xlsm and finviz.csv (apart from above mentioned files of course).

 

[dilshod_k]

I already tried the macro with your file and it works.


Maybe some of the macro is not being copied correctly.


I attach my file with the macro, just put the file of the macro and your csv file in a folder for example:

""C:\Users\User\Downloads" (whatever it is, but which is different from the destination file folder.)

and the other files in the folder:
"C:\Users\User\Downloads\Trading\Test\SpeedUp Tuning\New folder"

The macro is already updated, do not modify it, just execute the macro.

https://www.dropbox.com/s/rxnesu6k12akn3u/Master.xlsm?dl=0

Try and tell me.

I think I found the reason why it was not working with me even without modifications to the code. From the very beginning I was planning to run the code from the file called DataFile.xlsm and it would always give error message I mentioned before. When I tried to rename your Master.xlsm file (which was working perfectly well) into DataFile.xlsm it stopped working and was giving the same error message: Run-time error ‘91’: Object variable or With block variable not set.
With any other modifications of the name like FileData, DataFile1, Data, File, e.t.c IT IS WORKING! I'm not a programmer so I do not know the reason. Maybe you know the reason why?