Hello all
I am new to VBA and need some assistance with an issue.
Situation: I am currently working on creating a spreadsheet which will help my business tide over a few invoice payment concerns. These invoices are paid monthly, quarterly, semi annually or annually and I am trying to capture monthly data from 2005 - 2016.
I have created 7 sheets: two search features and one for each data element I plan on capturing with columns for each of the periods in question (230 columns in total on each spreadsheet (from B to HU and 120 rows). Each column is a month, quarter, semi annual date or annual date:
1) Single invoice search :
displays only one invoice based on below user inputs which are Vendor Name, Account Number, and date of invoice (again this can be monthly, quarterly, semi annually or annually so for example for 2008, these are Jan 08, Q1 08, 1H 08 and Annual 2008). I have created drop-down menus for each of the inputs and have also created a key based on a combination of the Vendor Name and account number in this sheet, for example: Kohls - 00102033333 etc.
2) Multiple invoice search:
displays all invoices for a particular vendor and account number.
3) Input - Invoice amount:
230 columns for dates and 120 rows with the aforementioned key
4) Input - Payment amount:
230 columns for dates and 120 rows with the aforementioned key
5) Input - Invoice date
230 columns for dates and 120 rows with the aforementioned key
6) Input - Payment date
230 columns for dates and 120 rows with the aforementioned key
7) Input - Invoice number
230 columns for dates and 120 rows with the aforementioned key
The user is currently expected to enter the invoice details on each of the 5 input sheets above and these are linked via vlookups to the search sheets
Problem:It is very time consuming for users to input details on 5 different sheets.
Assistance Needed : I would like to create 1 Input sheet where the user can input all of these details, which would then based on the key (vendor name - account number) and period selected (monthly, quarterly, semi annually or annually so for example for 2008, these are Jan 08, Q1 08, 1H 08 and Annual 2008) find the specific column and row on each of the 5 input sheets and paste the values in and save them down. Each time the user wants to input a new invoice, they simply select the new parameters (from the drop downs), enter the new details and these are again pasted in the precise row and column combination.
Seeking your assistance with the same.
I am new to VBA and need some assistance with an issue.
Situation: I am currently working on creating a spreadsheet which will help my business tide over a few invoice payment concerns. These invoices are paid monthly, quarterly, semi annually or annually and I am trying to capture monthly data from 2005 - 2016.
I have created 7 sheets: two search features and one for each data element I plan on capturing with columns for each of the periods in question (230 columns in total on each spreadsheet (from B to HU and 120 rows). Each column is a month, quarter, semi annual date or annual date:
1) Single invoice search :
displays only one invoice based on below user inputs which are Vendor Name, Account Number, and date of invoice (again this can be monthly, quarterly, semi annually or annually so for example for 2008, these are Jan 08, Q1 08, 1H 08 and Annual 2008). I have created drop-down menus for each of the inputs and have also created a key based on a combination of the Vendor Name and account number in this sheet, for example: Kohls - 00102033333 etc.
2) Multiple invoice search:
displays all invoices for a particular vendor and account number.
3) Input - Invoice amount:
230 columns for dates and 120 rows with the aforementioned key
4) Input - Payment amount:
230 columns for dates and 120 rows with the aforementioned key
5) Input - Invoice date
230 columns for dates and 120 rows with the aforementioned key
6) Input - Payment date
230 columns for dates and 120 rows with the aforementioned key
7) Input - Invoice number
230 columns for dates and 120 rows with the aforementioned key
The user is currently expected to enter the invoice details on each of the 5 input sheets above and these are linked via vlookups to the search sheets
Problem:It is very time consuming for users to input details on 5 different sheets.
Assistance Needed : I would like to create 1 Input sheet where the user can input all of these details, which would then based on the key (vendor name - account number) and period selected (monthly, quarterly, semi annually or annually so for example for 2008, these are Jan 08, Q1 08, 1H 08 and Annual 2008) find the specific column and row on each of the 5 input sheets and paste the values in and save them down. Each time the user wants to input a new invoice, they simply select the new parameters (from the drop downs), enter the new details and these are again pasted in the precise row and column combination.
Seeking your assistance with the same.
