#31
|
|||
|
|||
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 |
#32
|
|||
|
|||
Quote:
I believe the message box is displayed because it filters on the one vendor and cant find the next Vendor? |
#33
|
|||
|
|||
Quote:
|
#34
|
|||
|
|||
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! |
#35
|
|||
|
|||
Quote:
|
#36
|
|||
|
|||
OK, thanks for the update.
|
#37
|
|||
|
|||
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? |
#38
|
|||
|
|||
OK,
I'll check to see what happens. |
#39
|
|||
|
|||
Hi,
Lets try this one. I tested all file and it appears to work. Had to add a boolean for the new workbook. |
#40
|
|||
|
|||
That works really well. Thank you!!! What a huge timesaver.
|
#41
|
|||
|
|||
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 |
#42
|
|||
|
|||
Quote:
|
#43
|
|||
|
|||
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. |
#44
|
|||
|
|||
Quote:
|
#45
|
|||
|
|||
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 |