Creating multiple spreadsheets based on text

ksigcajun · #1 04-06-2015, 11:34 AM

Quote:

Originally Posted by charlesdh

Hi,

My error. Should have tested further.
Check this copy. It should copy data to the workbook and close it. Then it should open next workbook. If the next Vendor does not exist you will get the Msgbox.

It filters, copies the data correctly, the message box is displayed and that is it.

I believe the message box is displayed because it filters on the one vendor and cant find the next Vendor?

charlesdh · #2 04-06-2015, 11:30 AM

Hi,

I modified this part of the code.

Code:

On Error Resume Next
                Workbooks.Open Filename:=MyPath & "\" & Item & ".xlsx", UpdateLinks:=0
                If Err = "1004" Then '' this will be chaned when you determine if you want to create a new vendor workbook
                    MsgBox " Hi! At this point you need to create a new file for this Vendor will now exit sub"
                Exit Sub
                End If

ksigcajun · #3 04-06-2015, 11:36 AM

Quote:

Originally Posted by charlesdh

Hi,

I modified this part of the code.

Code:

On Error Resume Next
                Workbooks.Open Filename:=MyPath & "\" & Item & ".xlsx", UpdateLinks:=0
                If Err = "1004" Then '' this will be chaned when you determine if you want to create a new vendor workbook
                    MsgBox " Hi! At this point you need to create a new file for this Vendor will now exit sub"
                Exit Sub
                End If

Didnt change anything, that I saw. Still filters, copies, message box is displayed and thats it.

charlesdh · #4 04-07-2015, 12:04 PM

Hi,

I now have this file open vendors file and populate it. It will or should create a new vendor file and populate it. The code will also remove data in sheet2.
The repopulate it.
I created a "Template for the new Vendors".
You need to correct the path for the new vendor workbook. I made remarks in the code module for this.
Be sure you put all file in the same path!

ksigcajun · #5 04-08-2015, 09:36 AM

Quote:

Originally Posted by charlesdh

Hi,

I now have this file open vendors file and populate it. It will or should create a new vendor file and populate it. The code will also remove data in sheet2.
The repopulate it.
I created a "Template for the new Vendors".
You need to correct the path for the new vendor workbook. I made remarks in the code module for this.
Be sure you put all file in the same path!

Thank you sir! I'll be testing this out later today.

charlesdh · #6 04-08-2015, 10:48 AM

OK, thanks for the update.

ksigcajun · #7 04-09-2015, 11:42 AM

Quote:

Originally Posted by charlesdh

OK, thanks for the update.

Couple of things...

After the first workbook is created and copied, the macro doesnt populate anymore workbooks after that. It creates, saves but everything is blank.

Also, I understand it wont work for Vendors only with one line, but it didnt create a workbook for the last vendor.

Any suggestions?

charlesdh · #8 04-09-2015, 12:08 PM

OK,

I'll check to see what happens.

charlesdh · #9 04-09-2015, 12:31 PM

Hi,

Lets try this one. I tested all file and it appears to work. Had to add a boolean for the new workbook.

ksigcajun · #10 04-09-2015, 01:30 PM

Quote:

Originally Posted by charlesdh

Hi,

Lets try this one. I tested all file and it appears to work. Had to add a boolean for the new workbook.

That works really well. Thank you!!! What a huge timesaver.

charlesdh · #11 04-09-2015, 03:59 PM

There is 1 thing about this code it will over write sheet1. If this is a problem you can use this line of code.

Code:

if Fpass = False And ActiveWorkbook.Sheets("Sheet1").Range("D8").Text = " " Then '' added the "and" statement
                        ActiveWorkbook.Sheets("Sheet1").Range("D8").Value = ws.Cells(Dcell.Row, 1).Text
                        ActiveWorkbook.Sheets("Sheet1").Range("D12").Value = ws.Cells(Dcell.Row, 2).Text
                        Fpass = True
                    End If

ksigcajun · #12 04-13-2015, 10:05 AM

Quote:

Originally Posted by charlesdh

There is 1 thing about this code it will over write sheet1. If this is a problem you can use this line of code.

Code:

if Fpass = False And ActiveWorkbook.Sheets("Sheet1").Range("D8").Text = " " Then '' added the "and" statement
                        ActiveWorkbook.Sheets("Sheet1").Range("D8").Value = ws.Cells(Dcell.Row, 1).Text
                        ActiveWorkbook.Sheets("Sheet1").Range("D12").Value = ws.Cells(Dcell.Row, 2).Text
                        Fpass = True
                    End If

When does it overwrite Sheet1? I havent experienced that when running the code.

charlesdh · #13 04-13-2015, 10:14 AM

Hi,

Prior to this modification each time you open the vendors workbook the code will over write sheet1. The reason is we have the Fpass set to false when you open it.
To over come this I add a bit of code to looks at sheet1 to see if it is already populated. If it is the the code will not over write the existing data.

ksigcajun · #14 04-13-2015, 10:30 AM

Quote:

Originally Posted by charlesdh

Hi,

Prior to this modification each time you open the vendors workbook the code will over write sheet1. The reason is we have the Fpass set to false when you open it.
To over come this I add a bit of code to looks at sheet1 to see if it is already populated. If it is the the code will not over write the existing data.

Understood and thank you. Ill add the code and see if it runs successfully for me.

ksigcajun · #15 04-13-2015, 11:52 AM

Quick question, Im thinking of using this macro in a Userform. If I added the years (2015, 2016, 2017 and 2018) to Column F of Sheet1 would it be easy for the macro to see the year in ComboBox1 and run it for that specific year?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating a table that automatically updates based on entries of a heading in the document	cahphoenix	Word	3	10-29-2014 01:11 PM
I need to add multiple values based on multiple criteria in a cell not sure what to do	AUHAMM	Excel	3	10-27-2014 09:11 PM
Need help creating a Word document that is populated based on certain selected values	alidaanish	Word	1	01-10-2014 10:44 PM
Creating Report based on 'Task Summary' and 'Sub Tasks'	alijahed	Project	1	02-01-2013 04:20 AM
Creating formula based on if data is correct in cell	MattMurdock	Excel	1	08-06-2012 03:11 AM