Optional transfer of data to other workbooks or sheets

hyd1956 · Jun 30, 2020

Hi

I'm new to this and I'm trying to figure out how to get data to transfer to other sheets or workbooks automatically, when certain criteria is met.

I'm able to pull information through using formula's to see what is currently held in any column based on an identifying cell. For example entering the User ID, shows me name, telephone number etc stored in the master file.

I want to be able choose whether to override those cells with new information, but also in some circumstances I may need to move information to a new rows on the master sheet. I think this can probably be done using code for a command button, but I'm lost on exactly how to achieve it. I'm hoping someone here can help please. The basic functions I want from the button is the following:

The button is disabled unless a name is entered in B1
If there is name entered in B1 and B2 is 0 then enable the button.

Clicking the button should look for the next account number in the Master sheet where the status is which has a status of "Available" and the end date is greater than todays date. If nothing is available return a message saying there are no account numbers available.

If an account number is "Available" and the end date is less than todays date, it should then move the account number to a new line below the existing records, generate a new reference number and insert the name, telephone number, email address and date. It should set the last change action to "Created Account" and set the status as "not available".
It should then return a confirmation the action is completed and provide the reference number and the end date to the user.

If B1is entered, B2 is equal to or greater than 1 (meaning there is a match and the status will be assigned or closed) and b5 is 0 then I want to ask a yes or no question
If no is clicked then it should return "You cannot proceed"
If Yes is clicked find the duplicate record only one will have a status of assigned that has an account number attached to it and check if the end date has passed. If the end date has not passed, change the original record to "Closed" whilst adding, the user ID, closed reason as "renewed", Last change reason as "chose to renew" and the date in last change date fields.
It should then generate a new line in master sheet, moving the previous account number down but creating a new reference number, add the customer name + telephone number, email address, add the status as assigned, the user ID, current date and last change reason as "new reference created for renewal".
The user should get confirmation its completed and be provided with reference number and the end date.

Examples of the user and master sheet

[

exceltest.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Enter Name

TestName

User ID

myname

2

Number of MPAN Entries

3

Date

today

Button - see sheet for furher details?

3

Enter Phone Number

123

Add Email Address

test@test.com

4

Combined

TestName | 123

5

Name + Phone numer Entries

1

6

7

8

Name + Phone Check

9

Master Sheet Row number

Account Number

Reference Number

Name

Telephone

Name + Telephone

email address

Status

end date

Reason for change

Last Change

Date Of Last Change

Last Change Completed by

10

11

12

13

14

15

16

17

18

19

Name Check

20

Master Sheet Row number

Account Number

Reference Number

Name

Telephone

Name + Telephone

email address

Status

endate

Reason for change

Last Change

Date Of Last Change

Last Change Completed by

21

22

23

24

25

26

27

28

Sheet2

Cell Formulas
Range	Formula
B2	B2	=COUNTIF(Sheet3!$C:$C,$B$1)
B4	B4	=$B$1&" \| "&$B$3
B5	B5	=COUNTIF(Sheet3!$E:DF,$B$4)
B22:O28,B11:O18	B11	=IFERROR(INDEX(Sheet3!A:A,$A11),"")
A11:A17	A11	=MATCH($B$4,OFFSET(Sheet3!$E$1,$A10,,20000),0)+$A10
A22:A27	A22	=MATCH($B$1,OFFSET(Sheet3!$C$1,$A10,,20000),0)+$A10