Riaaz66
02-22-2018, 06:55 AM
Hi,
I need urgent help with the following.
I have a MasterWorkbook, where I want to write VBA code that should perform some routine tasks. I know it is a lot but I am kind of lost how to approach this in VBA code.
- The MasterWorkbook macro should process files by looping from a specific folder (this folder differs every month). Let's call these files (all xlsx file format) DataWorkbooks.
- Each DataWorkbook contains 1,2 or 3 sheets (based on a count value in another sheet in the DataWorkbook) from where the dynamic ranges (always Range("A2:O??")) should be copied to 2 sheets in the MasterWorkbook in cell A7.
(I want to use the codename of the sheet because the workbook and sheetname can differ on each DataWorkbook in that folder.)
- When the copying has been done, the DataWorkbook should be closed without saving BUT the MacroWorkbook need to saveAs or Save CopyAs a new filename to another location
- The filename will be based on a cell value (where the filename is specified).
- The original MasterWorkbook should not be saved and remain open.
So to simplify (I hope it is):
1. MasterWorkbook is manually opened. (Macro or VBA code need to be written)
2. Run Macro from MasterWorkbook:
A. Open Specified folder (point folder to open)
B. LOOP in Folder and open each DataWorkbook (xlsx file)
C. On each DataWorkbook GOTO Sheet1, COUNT number of loans in column C, Set Countvalue in MacroWorkbook in Sheet1 Cell "A1)
D. Now - when in Sheet1 cell "A1" the value is 1 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7
- when in Sheet1 cell "A1" the value is 2 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7 AND
copy FROM DataWorkbook.Sheet4. Dynamic Range A2:O?? TO MacroWorkbook Sheet3.Cell A7
- when in Sheet1 cell "A1" the value is 3 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7 AND
copy FROM DataWorkbook.Sheet4. Dynamic Range A2:O?? TO MacroWorkbook Sheet3.Cell A7 AND
copy FROM DataWorkbook.Sheet5. Dynamic Range A2:O?? TO MacroWorkbook Sheet4.Cell A7
- when in Sheet1 cell "A1" the value is > 3 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7 AND
copy FROM DataWorkbook.Sheet4. Dynamic Range A2:O?? TO MacroWorkbook Sheet3.Cell A7 AND
copy FROM DataWorkbook.Sheet5. Dynamic Range A2:O?? TO MacroWorkbook Sheet4.Cell A7 AND
Create Logfile (with same filename of the DataWorkbook but with "Logfile" as prefix) in TXT format in same location of Specified Folder with message "Contains more than 3
E. When all copying is done, close DataWorkbook with no saving.
F. "SaveAs" or "CopyAs" the MacroWorkbook as "MacroWorkbook_processed & filename [based on MacroWorkbook.Sheet1.Range.("B3").text] & DateTime Now()" (eg. MacroWorkbook_processed_LOAN12345_22022018_14h23m)
BUT!!! The original MacroWorkbook from where the is executing these action should remain the same and open.
G. NEXT DataWorkbook
Can somebody help me with this?
Kind regards,
I need urgent help with the following.
I have a MasterWorkbook, where I want to write VBA code that should perform some routine tasks. I know it is a lot but I am kind of lost how to approach this in VBA code.
- The MasterWorkbook macro should process files by looping from a specific folder (this folder differs every month). Let's call these files (all xlsx file format) DataWorkbooks.
- Each DataWorkbook contains 1,2 or 3 sheets (based on a count value in another sheet in the DataWorkbook) from where the dynamic ranges (always Range("A2:O??")) should be copied to 2 sheets in the MasterWorkbook in cell A7.
(I want to use the codename of the sheet because the workbook and sheetname can differ on each DataWorkbook in that folder.)
- When the copying has been done, the DataWorkbook should be closed without saving BUT the MacroWorkbook need to saveAs or Save CopyAs a new filename to another location
- The filename will be based on a cell value (where the filename is specified).
- The original MasterWorkbook should not be saved and remain open.
So to simplify (I hope it is):
1. MasterWorkbook is manually opened. (Macro or VBA code need to be written)
2. Run Macro from MasterWorkbook:
A. Open Specified folder (point folder to open)
B. LOOP in Folder and open each DataWorkbook (xlsx file)
C. On each DataWorkbook GOTO Sheet1, COUNT number of loans in column C, Set Countvalue in MacroWorkbook in Sheet1 Cell "A1)
D. Now - when in Sheet1 cell "A1" the value is 1 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7
- when in Sheet1 cell "A1" the value is 2 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7 AND
copy FROM DataWorkbook.Sheet4. Dynamic Range A2:O?? TO MacroWorkbook Sheet3.Cell A7
- when in Sheet1 cell "A1" the value is 3 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7 AND
copy FROM DataWorkbook.Sheet4. Dynamic Range A2:O?? TO MacroWorkbook Sheet3.Cell A7 AND
copy FROM DataWorkbook.Sheet5. Dynamic Range A2:O?? TO MacroWorkbook Sheet4.Cell A7
- when in Sheet1 cell "A1" the value is > 3 THEN
copy FROM DataWorkbook.Sheet3. Dynamic Range A2:O?? TO MacroWorkbook Sheet2.Cell A7 AND
copy FROM DataWorkbook.Sheet4. Dynamic Range A2:O?? TO MacroWorkbook Sheet3.Cell A7 AND
copy FROM DataWorkbook.Sheet5. Dynamic Range A2:O?? TO MacroWorkbook Sheet4.Cell A7 AND
Create Logfile (with same filename of the DataWorkbook but with "Logfile" as prefix) in TXT format in same location of Specified Folder with message "Contains more than 3
E. When all copying is done, close DataWorkbook with no saving.
F. "SaveAs" or "CopyAs" the MacroWorkbook as "MacroWorkbook_processed & filename [based on MacroWorkbook.Sheet1.Range.("B3").text] & DateTime Now()" (eg. MacroWorkbook_processed_LOAN12345_22022018_14h23m)
BUT!!! The original MacroWorkbook from where the is executing these action should remain the same and open.
G. NEXT DataWorkbook
Can somebody help me with this?
Kind regards,