Saving a file based on list in another file

%3CLINGO-SUB%20id%3D%22lingo-sub-1831988%22%20slang%3D%22en-US%22%3ESaving%20a%20file%20based%20on%20list%20in%20another%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1831988%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20a%20weekly%20basis%2C%20I%20receive%20a%20list%20of%20projectcodes%20from%20our%20ERP%20system%20(in%20.xlsx%20format).%20I%20have%20another%20formatsheet%2C%20in%20which%20I%20can%20fill%20in%20(in%20cell%20C5)%20the%20projectnumber.%20Once%20the%20projectnumber%20has%20been%20filled%20in%2C%20a%20projectreport%20and%20hourreport%20is%20being%20imported%20from%20two%20other%20sheets.%20I%20have%20to%20do%20some%20corrections%20within%20the%20format%20sheet%20and%20save%20it%20as%20%22PBO%20%5B%2B%20projectnumber%5D%20%2B%20ddmmyyyy%22%20(e.g.%3A%20%22PBO%2020PPR1234%2029102020%22).%20I%20copy%20the%20format%20file%20and%20fill%20in%20the%20projectnumber%20by%20hand%20and%20save%20it%20by%20hand.%20However%2C%20I%20want%20this%20to%20be%20done%20automatically.%20So%20what%20I%20want%3A%3CBR%20%2F%3E%3CBR%20%2F%3E1.%20The%20list%20of%20projectnumbers%20is%20being%20received%20from%20the%20ERP%20system.%20(~10-20%20numbers)%3CBR%20%2F%3E2.%20For%20all%20numbers%2C%20a%20file%20is%20being%20generated%20with%20the%20projectnumber%20in%20cell%20C5%20and%20the%20above%20mentioned%20title%20format%3C%2FP%3E%3CP%3E3.%20I%20can%20open%20and%20check%20the%2010-20%20files%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%2C%20I%20managed%20to%20include%20a%20macro%20that%20saves%20the%20format%20file%20based%20on%20the%20projectnumber%20in%20C5.%20However%2C%20I%20have%20to%20create%20the%20files%20manually%20and%20fill%20in%20the%20projectnumber%20by%20hand.%20Does%20someone%20have%20any%20clue%20how%20to%20link%20the%20format%20file%20with%20the%20automatic%20print%20of%20the%20ERP%20system%3F%20Massive%20thanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1831988%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

Hi,

 

On a weekly basis, I receive a list of projectcodes from our ERP system (in .xlsx format). I have another formatsheet, in which I can fill in (in cell C5) the projectnumber. Once the projectnumber has been filled in, a projectreport and hourreport is being imported from two other sheets. I have to do some corrections within the format sheet and save it as "PBO [+ projectnumber] + ddmmyyyy" (e.g.: "PBO 20PPR1234 29102020"). I copy the format file and fill in the projectnumber by hand and save it by hand. However, I want this to be done automatically. So what I want:

1. The list of projectnumbers is being received from the ERP system. (~10-20 numbers)
2. For all numbers, a file is being generated with the projectnumber in cell C5 and the above mentioned title format

3. I can open and check the 10-20 files 

 

Currently, I managed to include a macro that saves the format file based on the projectnumber in C5. However, I have to create the files manually and fill in the projectnumber by hand. Does someone have any clue how to link the format file with the automatic print of the ERP system? Massive thanks in advance!

 

1 Reply
Highlighted

@wvanwesel 

 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. You could get a precise solution much faster with a file (w/out sensitive data). This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

 

* Knowing the Excel version and operating system would also be an advantage.

 

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)