extract excel sheet and save in reference folder

pl anyone can help guru's..??

hllo.not yet any response..?

Can you perhaps clarify the following:

- Are there already folders named after each company in Sheet3 column C?
- Am I understanding correctly that each of those folders contains a workbook called 'UM 01' or whatever the associated UM is?
- Do those workbooks already contain data exactly like you've shown in Sheet2?
- What is the file path of one of those workbooks?

This sort of procedure isn't one that anyone other than you can properly test, so it'll be easier to find assistance if you can be precise about where the data is ultimately going.

moreover i made these folders with below VBA..may this will clear your picture more..

Sub Create_Folders_Desktop()
'create main folder in desktop
CreateObject("Scripting.FileSystemObject").CreateFolder (CreateObject("WScript.Shell").specialfolders(4) & "\Main")
'get path for new folder
MainFolderPath = CreateObject("WScript.Shell").specialfolders(4) & "\Main" & "\"
'put range with new foldernames in array
sn = Sheets("Sheet1").Cells(1).CurrentRegion.Value
'check and replace invalid characters
For i = 2 To UBound(sn)
For Each v In Array("/", "\", "|", ":", "*", "?", "<", ">", """")
sn(i, 3) = Replace(sn(i, 3), v, "_")
Next
Next
'create new folders and subfolders
For i = 2 To UBound(sn)
MkDir MainFolderPath & sn(i, 2)
MkDir MainFolderPath & sn(i, 2) & "\" & sn(i, 3)
Next
'write array back to sheet to check replacement of invalid characters in new foldernames
Sheets("Sheet1").Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

hi contosh..happy to see u after long time...
yes there is already folder at desktop with the name of Main...for example...c:\desktop\Main\UM 01\AEG TRAVEL SERVICES (PVT) LTD , c:\desktop\Main\UM 02\ASAD PACKAGES

no folder has data or any excel sheet..Main folder has all UM 01 to UM 86 folders, and each sub folder has another folder with company name..which i mentioned in sheet 3..

Gondal

Give the procedure below a try. It works for me on a shortened sample size. Be sure that the "MyPath" line of the code matches your file path as far as the "Main" folder. Let me know how it goes...

Sub SaveByCoName()
    Dim wbSource As Workbook
    Dim MyPath As String
    Dim wb As Workbook
    Dim i As Long
    Dim LastRow3 As Long, LastRow1 As Long
    Dim UM As String
    Dim ContName As String
    Application.ScreenUpdating = False

    MyPath = "C:\Desktop\Main\"  'MAKE SURE THIS MATCHES YOUR FILE PATH
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    Set wbSource = ActiveWorkbook
    LastRow3 = wbSource.Sheets(3).Cells(Rows.Count, "B").End(xlUp).Row
    LastRow1 = wbSource.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To LastRow3
        UM = wbSource.Sheets(3).Range("B" & i).Value
        ContName = wbSource.Sheets(3).Range("C" & i).Value
    wbSource.Sheets(1).Cells.AutoFilter
    With wbSource.Sheets(1).Range("A1:J" & LastRow1)
        .AutoFilter
        .AutoFilter Field:=10, Criteria1:=UM
        .SpecialCells(xlCellTypeVisible).Copy
    End With
       
    Set wb = Workbooks.Add
    wb.Sheets(1).Range("A1").PasteSpecial
    wb.SaveAs (MyPath & UM & "\" & ContName & "\" & ContName & ".xlsx")
    wb.Close
Next i
wbSource.Sheets(1).Cells.AutoFilter
Application.ScreenUpdating = True
End Sub

hi cantosh
when i run this VBA on sheet 1,this is only desperate the UM 01 and more over also remove the remaining data of original sheet only shows UM 01.


Gondal

Make sure folders are created with names on Sheet3. (Run top macro to achieve this)
Then run second macro to create seperate files in each folder.

hi bakerman..
its works till UM 41.. after shows error and did not run again..

Gondal

That's why I told you to create folder structure according to sheet3 first.
The problem lies in PROFESSIONALS' ACADEMY OF COMMERRCE. After running first folder-create macro name is changed into PROFESSIONALS_ ACADEMY OF COMMERRCE and then it works fine.

or cross the miss match and copy all other and shows miss sheet at end and also miss the folder if already sheet exist....

is this possible that i will change the name of sub folder instead of company with 01,02,03 than run macros for copy data after change the excel sheet name with company name from 01,02,03...to avoid such problem again again...

As Bakerman mentioned, the trick is that your folder structure must exactly match what you have in Sheet3. His 'Create_Folders_Desktop' procedure is a smart addition. The macro I posted works for me, but I created all of the folders from scratch based on Sheet3, whereas you have something already in place. Make sure all of the folder names line up perfectly with Sheet3 and you should get accurate results.

ok well cantosh/bakerman..i have changed, i have create different folder with company name to numeric like: c:\desktop\Main\UM 01\1.....can u make necessary changes in VBA to copy data and avoid duplicate sheet if already exist.if possible ..??

You can't keep changing your mind.
I've made you a new example file in which company names are all set to wright format on Sheet3.
When in the future you want to add a new customer I also added a small Userform which shows when you activate sheet3. Just fill in customernumber and customercompany.
The name is automatically checked on unautherized characters and adapted to proper format, a new UM number is assigned and a new folder is created.
I changed format of UM numbers to 000 so you can add up to 999 customers.
So all you have to do is create Foldertree with first code and then run second code to create files in each folder and 20 sec later you're done.

thnx bakerman..your help is very much valuable for me..bundle of thnx

Dear bakerman
i am facing one problem in sheet when i go in sheet 3 and enter info its create 2 same number cust and company.. pl guide if i update sheet 3 after that i will run macros again of create sheet..or it will pick automatically....thnx

Added 2 checks on userform. First number is checked for duplicates afterwards companyname. When all is allright then name is added to list and UM-folder and company subfolder are created and userform disappears automatically. When nuber or name are duplicate userform stays active so you know something is wrong.

thnx for helping out me, when i update sheet 3, its create new folder and update sheet 3 as my per my info provided and also create folder in path but when i update sheet 1 its not create sheet in ref folder, if i run macros again it asked for replace for sheet, if i make yes file replaced then its asked again for 2nd sheet and continue asked and if say no its showing error and held sheet..pl guide further regarding this

Gondal

bakerman.??

Your initial question has already been solved so what is it you want to achieve now ?
If you want to create new file and replace old one every time you add a row on sheet1 I'd create a Userform to do so since you then have more control on how names are inserted so less chance on error by wrong name etc... Up until now however I've not seen too much input from your end so I expect some more from now one. This forum is here to assist on whatever project, not to create a working application for you. For that there are commercial services available.

Okay, added second Userform to fiil in your data on sheet1.
When you select company from combobox then cust-number, UM-number and company-address are automatically filled.
Finish all fields and click button. All data is added in bottom-row and new file is created in folder/subfolder from company, containing latest changes.

sorry for disturbing bakerman..on sheet 1 no uniform is showing and when i entered data in sheet 3 in uniform ,no UM number is created and folder/sun folder and also no company address filled


Gondal

Updated version.

thnx bakerman such a kind help

Good luck and thanks for the rep.