Hello everybody
I have a folder that contains eight excel files (one main workbook where I need to put the code)
I need to Export data from the main sheet to many open files based on the names of the sheets
this issue seems easy but I tried and failed to achieve it
Hereby I attached an My project that almost matches to My original project
Note : the original data is about 20,000 rows so I am searching for faster way for this task
Your help will be really appreciated. Thanks in advance
Last edited by Joky; 02-10-2018 at 12:42 AM.
See if this does what you want. It might hiccup on the paste part because of the file name. I am not sure how you have it structured in column AJ, but you can probably work that part out. The rest of the code tested OK.
Please Login or Register to view this content.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
Hello Mr JLGWhiz
Many thanks for your help
I have tested the code but data is not exported ... Is there another way
Thanks advanced for help
Nope, that is about all I have based on data provided.Originally Posted by Joky
Regards, JLG
You could try this (First get rid of the "Merge & Center" and "Wrap Text" cells in all workbooks)
Please Login or Register to view this content.
try this:
Please Login or Register to view this content.
Hi jolivanes
I think we need a special code to Clear all the files to which the data was exported
From row 8 and last row of data except the main sheet In preparation for receiving new data
Thank you so much for your efforts on helping me with this project.
Best regards for all of you
Last edited by Joky; 02-10-2018 at 12:29 AM.
Insert this after the "If Workbooks(j).Name <>" line
Re: My friend jolivanes Your solution is to export data to open filesPlease Login or Register to view this content.
Re: My friend ikboy Your solution is to export data to closed files
I am sure we were aware of that as we cobbled the code together.
But you asked for open files, and that's what you got.
Hi jolivanes
Thanks a lot for your great efforts
This is not intended
I mean creating a new code to clear all open files ... This code will be executed after finishing the work
Best Regards
Last edited by Joky; 02-10-2018 at 12:30 AM.
what is the difference? It clears the open workbook before anything is pasted into it.
If you want to clear them separately, exclude whatever workbooks you want excluded (see Post #5) and use the code from Post #8.
Maybe it would not be a bad idea to start experimenting yourself.
Please Login or Register to view this content.
@ikboy
What happens with the ClearContents if there is no data in the workbook, only the headers on row 7?
@Jolivan
First my poor English no fully understanding Joky's want.
I just guess he want to transfer the data from main to files for keep records.
I think if new data have to accumulate to existing files.
So my code clears the source data and at the same time transfer the data to files.
To avoid duplicate write to existing files.
Hi ikboy
I just need to create a special code to clear all closed files except the main sheet
I hope the idea is clear
Best regards for all of you
Last edited by Joky; 02-10-2018 at 12:30 AM.
All help is much appreciated.
Show us the different codes you put together and tried.
@ikboy
This line
will clear row #7 if there is no data, with the exception of row 7, in the file.Please Login or Register to view this content.
I personally find that when you include the header row and then offset(1) is safer so as not to delete, or clear, the headers
@jolivanes,
You right, I add below code at the first line for checking data before run all.
Please Login or Register to view this content.
@Joky
Sorry over look your #15.
Please Login or Register to view this content.
@jolivanes
Appreciate any direction or code you might share.
Best Regards from the deep of my heart
Last edited by Joky; 02-03-2018 at 09:59 PM.
Hi jolivanes
That's wonderful and fascinating
Best Regards from the deep of my heart
Last edited by Joky; 02-10-2018 at 12:31 AM.
Welcome ikboy
Sorry for disturbing you again
I want to add the total formula in the third row in all files except the main sheet
I have attached a sample to clarify
Thanks a lot for your patience
Last edited by Joky; 02-10-2018 at 12:32 AM.
try this:
Please Login or Register to view this content.
Hi ikboy
The last point now for me is How do I deal with all of the files closed directly without adding workbooks automatically?
Because I will put some code in those files
I really appreciate you taking the time to help me on this.
Best Regards from the deep of my heart
Last edited by Joky; 02-10-2018 at 06:09 AM.
find the code ActiveWorkbook.Close and delete it.
Welcome ikboy
In fact the words would not be enough to thank you .. You are really wonderful
Have a nice time
Best Regards
Welcome ikboy
Please add a line to sort all files depending on the column B & E
Thank you very very much
Best Regards
Last edited by Joky; 02-05-2018 at 09:19 PM.
Please Login or Register to view this content.
Thank you very much for great and awesome support Mr. ikboy
You are wonderful and I like your approach in coding
Best Regards from the deep of my heart
Hi ikboy
Is it possible to export this code to the code editor for all files?
Can it be done easily or it is difficult?
Thank you very very much
Last edited by Joky; 02-10-2018 at 12:33 AM.
Not sure, difference files might have difference layout. You can try it first, if cannot use open new thread to forum.
Hi ikboy
In fact this issue seems easy but I tried and failed to achieve it
I forgot to remind you that I need to add another line to the numbering automagically in all files
Again, thanks for your kind help
Best Regards
Last edited by Joky; 02-06-2018 at 10:34 PM.
Open new thread and make a simulation file to show your request.
Hi ikboy
Here's this attachment
Thank you very much for helping me
Last edited by Joky; 02-10-2018 at 12:33 AM.
Joky,
Some points have to explain:
1. Having the “numbering” in the main file?
2. If #1 no have, how to get the or where to extract it?
Hi ikboy
I just needed to add your code to adjust the automatic numbering in all files to become sequential order
That's all...The attachment has been modified for clarification..Please have a look
Thanks a lot for your patience in this issue
Last edited by Joky; 02-10-2018 at 06:04 AM.
I have new questions. The Example G has 2 new columns with title (S & ID), but those without appear in main file.
Do you want me to amend the code when export data from “main” to Examples files add 2 columns let you to add “ID” data or you will amend “main” file added that/those column a/column b with data?
Hi ikboy
I do not mean anything at all
Please run the code and you will notice what I want ... All I need only points referred to
Thank you so much for your efforts on helping me with this project.
Last edited by Joky; 02-10-2018 at 12:38 AM.
HTHPlease Login or Register to view this content.
Hello Mr. ikboy
sorry for the late
If possible : I need another request here
I need to insert the number of 15 sheets in all files
Best Regards from the deep of my heart
Last edited by Joky; 02-10-2018 at 12:34 AM.
Assume the data extracts to sheet 1. If not, please amend the words in red as you wanted.
Clear all tested files and test again.
Sub zz()
If Cells(Rows.Count, 1).End(xlUp).Row < 8 Then MsgBox "No data for ...": Exit Sub
Application.ScreenUpdating = 0
Dim ar, br(), f$, p$, d As Object, title As Range, Myr&, zc&, i&, j&, k, t, affirm
p = ThisWorkbook.Path & "\"
Set d = CreateObject("scripting.dictionary")
Set title = Range("a6:aj7")
ar = Range("a8:aj" & Cells(Rows.Count, 1).End(3).Row).Value
zc = UBound(ar, 2)
For i = 1 To UBound(ar)
d(ar(i, zc)) = d(ar(i, zc)) & "|" & i
Next
For Each k In d.keys
t = Split(d(k), "|")
ReDim br(1 To UBound(t), 1 To zc)
For i = 1 To UBound(t)
For j = 1 To zc
br(i, j) = ar(t(i), j)
Next
Next
If Len(Dir(p & k & ".xlsx")) Then
affirm = MsgBox("Record of " & k & " existing, accumulate to it or not?", vbYesNo)
f = Dir(p & k & ".xlsx")
Workbooks.Open p & f
Sheets(1).Activate
Myr = Cells(Rows.Count, 3).End(3).Row
If affirm = vbYes Then
Cells(Myr + 1, 1).Resize(UBound(br), zc) = br
Myr = Myr + UBound(br)
Else
Cells(8, 1).Resize(Myr, zc).Clear
Cells(Rows.Count, 1).End(3)(3).Resize(UBound(br), zc) = br
Myr = 7 + UBound(br)
End If
GoSub Fula
ActiveWorkbook.Save
Else
Workbooks.Add 1
For i = 1 To 14
Sheets.Add after:=Sheets(i)
Next
Sheets(1).Activate
title.Copy Cells(6, 1)
Cells(8, 1).Resize(UBound(br), zc) = br
Myr = 7 + UBound(br)
GoSub Fula
ActiveWorkbook.SaveAs p & k, FileFormat:=51
End If
Rem ActiveWorkbook.Close 'if you want to automatic close file, remove the words REM that in front of this line.
Next
Application.ScreenUpdating = 1
MsgBox "Done"
End
Fula:
Range(Cells(8, 1), Cells(Myr, zc)).Sort key1:=Cells(8, "d"), key1:=Cells(8, "g"), order1:=1, order2:=1, Header:=0
Range(Cells(3, 2), Cells(3, zc - 1)).FormulaR1C1 = "=Sum(R8C:R" & Myr & "C)"
For i = 8 To Myr
Cells(i, 1) = i - 7
Next
Columns("a:aj").AutoFit
Return
End Sub
Hello Mr. ikboy
how to save the files in the XML based and macro enbled file format in order to put some code in order for it to run
Because I put some code in the editor and did not work
Thank you very very much
Last edited by Joky; 02-10-2018 at 12:35 AM.
Sorry, I don't know how to save as xml format. You such create new thread let others helper help you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks