Copy paste data to multiple workbooks based on condition

[Natsha]

Hi all,

Kindly help

I have data in the sheets, I need the data to be copied to multiple workbooks based on condition

In column J , it has 5 category , sometimes three

*US FM TRAVEL BOA
*US FM PCARD BOA
*US GSC PCARD BOA
*US GSC TRAVEL BOA
*CA FM CAN TRAVEL BOA

I need this data to be pasted into three new books and stored in one folder as per below condition

*CA FM CAN TRAVEL BOA to one workbook in sheet1

*US FM PCARD BOA and *US FM TRAVEL BOA to one workbook in sheet1 , if instead of this two category only one is there i want that alone to paste it one workbook in sheet1

*US GSC PCARD BOA and *US GSC TRAVEL BOA to one workbook in sheet, if instead of this two category only one is there i want that alone to paste it one workbook in sheet1

I don't want macro to be placed only in one macro workbook... i need to run the code in any of the excel file

Additionally _ if could provide the file path data in folder in excel sheet in sheet2 , it would be great to open it

 

[Natsha]

Please help....anyone

 

[vletm]

Natsha
This seems to work as You've written.
... without ... additionally ...
You'll find that code from Sheet1-code.

 

[Natsha]

Hi,

It's works well...you been helping me lot..no words

Thank you so much

 

[Natsha]

Hi,

I have added the code to addin ..
I have opened new workbook and pasted the data in sheet1 and tired running but it doesn't work ... Can you help me so that I can use the macro in any workbook..
I am getting error here in the below line
awn = Left(wb1, worksheetfunction.Find(".",wb1) -1)
Run time error 1004 says
Unable to get the find property of the worksheet function class

Please help

 

[Marc L]

Hi, as a reminder you should be more accurate in your initial post !​

​

According to your attachment an Excel basics VBA demonstration as a starting point (edit v2) :​

Sub Demo1()
         Dim V
    With Application
         V = .GetOpenFilename("Excel workbooks,*.xlsx"):  If V = False Then Exit Sub
        .DisplayAlerts = False
        .ScreenUpdating = False
    With Workbooks.Open(V, 0).ActiveSheet
        .[J1].Copy .[ZZ1]
         Workbooks.Add xlWBATWorksheet
     For Each V In Array("CA FM ", "US FM ", "US GSC ")
        .[ZZ2].Value2 = "~*" & V & "*"
        .[A1].CurrentRegion.AdvancedFilter 2, .[ZZ1:ZZ2], ActiveSheet.[A1]
         ActiveSheet.UsedRange.Columns.AutoFit
         ActiveWorkbook.SaveAs .Parent.Path & "\" & V, 51
         ActiveSheet.UsedRange.Clear
     Next
         ActiveWorkbook.Close False
        .Parent.Close False
    End With
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[vletm]

Natsha
with #4 reply You wrote It's works well
with #5 reply You did something
... here, it works
... what did You?
... with out clear details ... I won't even guess.
... based Your given ... line ... that paste is different than in my code.

 

[Natsha]

Hi, what I meant is the code runs perfectly if the workbook is macro workbook which you have given ( book5)

I didn't edit anything in the code , I just copied the full code and pasted to my personal macro workbook

If I try to run the macro in any other workbook ,any new workbook it's not working...
I am getting error here in the below line
awn = Left(wb1, WorksheetFunction.Find(".", wb1) -1)
Run time error 1004 pop up says
Unable to get the find property of the worksheet function class



Hope this clear..

 

[vletm]

Natsha
Now, You've different line pasted with red color.
You should send that code with Excel-file here.
Without it I won't comment or guess more.

 

[Marc L]

Please help....anyone

Try my post #6 VBA demonstration at least … ​

​

 

[Natsha]

Hi,

The thing is the code doesn't work on ready only excel file..

When I save and run it...it's works pretty well..it's sounds funny but it's the truth.

But what important is ... It's working.. thank you so much for putting up with me.

 

[Natsha]

Try my post #6 VBA demonstration at least … ​

​

Hi,

Thank for you helping

I copy pasted ur code and tried to run it..but why does it goes to folder...?

It's not what I mentioned think so..anyways tq

 

[Marc L]

As I wrote be accurate when creating a thread in order there is nothing to guess​

the reason why I let you to amend the « starting point » code for what you dit not well elaborate …​

​