using excel sheet1 to input data to sheet2

[tonylpcs@yahoo.co.uk]

Hi Everyone,

You have all been very helpful in the past i hope someone can help me now,
i have a spreedsheet and want to be able to use it to input details into sheet1 and have those details moved to sheet2 in a line with the relavent information moving to the correct colum, then sheet1 needs to clear down so we can input other details which will then be trasfered to sheet2 on the next line and so forth,

is this posible? and if so can someone give me a code or formula to do this with please?

just in case that wasnt 100% clear what i wanted to do i have attached some screen shots to show you, shot one is sheet1 and the blue areas will be the places i input the information,

shot 2 is of sheet2 and shows where i want this information to transfer to,

please help if you can thanks

Tony

Shot 1:

Excel Workbook
	A	B	C	D	E	F	G
3
4		INSURED
5		TRADE
6		INCEPTION			INSURER
7		EXPIRY			POLICY TYPE
8		TOTAL PREMIUM			DEPARTMENT
9		GROSS PREMIUM			SOURCE of Enquiry
10		IPT 5%			EXECUTIVE
11		INSURERS FEE			TYPE OF BUSINESS
12		FML FEE			DISCOUNT/AGENT PAYABLE
13		B'CARD FEE			PAYMENT METHOD
14		Commission Rate			DEPOSIT TYPE
15		B'CARD CHARGE			DEPOSIT AMOUNT
16		NET TO INSURER			PAYMENT DATE
17		Commission			PROMPT / PCL Ref
18		Total Brokerage			OPEN GI REF
19		REASON FOR DEBIT NOTE:
Debit Note 5% IPT

Shot2:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Cut Off Month	Customers Name	Incept	Net Premium	IPT	Comm %	Insurer Fee	FML Fee	Brokerage Earned	TYPE	Producer	Insurer	Open Gi Ref	Source of Enquiry	method payment	Date payment Recived / Expected On
2
3
4
5
6
7
8
SALES

thanks
Tony

 

[bertie]

I have assumed the following ranges on Sheet1 map to Sheet2:

Sheet2


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 87px"><COL style="WIDTH: 56px"><COL style="WIDTH: 64px"><COL style="WIDTH: 40px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 56px"><COL style="WIDTH: 85px"><COL style="WIDTH: 45px"><COL style="WIDTH: 81px"><COL style="WIDTH: 62px"><COL style="WIDTH: 64px"><COL style="WIDTH: 69px"><COL style="WIDTH: 70px"><COL style="WIDTH: 89px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 85px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Cut Off Month</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Customers Name</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Incept</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Net Premium</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">IPT</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Comm %</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Insurer Fee</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">FML Fee</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Brokerage Earned</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">TYPE</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Producer</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Insurer</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Open Gi Ref</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Source of Enquiry</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">method payment</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Date payment Recived / Expected On</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C7</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C4</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C6</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C8</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C10</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C14</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C11</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">C12</TD><TD style="BACKGROUND-COLOR: #000080; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 11pt">c18</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">F7</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">F8</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">F6</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">F18</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">F9</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">F13</TD><TD style="BACKGROUND-COLOR: #ccccff; FONT-FAMILY: Verdana; FONT-SIZE: 11pt">F16</TD></TR></TBODY></TABLE>




The code will have to be amended if the above mappings are wrong. Here is the code to process the form on Sheet1.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProcessForm()
   [COLOR=darkblue]Dim[/COLOR] wsForm [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsData [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] nextRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
 
   [COLOR=darkblue]Set[/COLOR] wsForm = Worksheets("Sheet1")
   [COLOR=darkblue]Set[/COLOR] wsData = Worksheets("Sheet2")
   nextRow = wsData.Range("A" & Rows.Count).End(xlUp).Row + 1
   [COLOR=darkblue]Set[/COLOR] rng = wsData.Range("A" & nextRow)
 
   [COLOR=green]'transfer the data[/COLOR]
   [COLOR=darkblue]With[/COLOR] wsData
      rng = wsForm.Range("C7").Value
      rng.Offset(, 1).Value = wsForm.Range("C4").Value
      rng.Offset(, 2).Value = wsForm.Range("C6").Value
      rng.Offset(, 3).Value = wsForm.Range("C8").Value
      rng.Offset(, 4).Value = wsForm.Range("C10").Value
      rng.Offset(, 5).Value = wsForm.Range("C14").Value
      rng.Offset(, 6).Value = wsForm.Range("C11").Value
      rng.Offset(, 7).Value = wsForm.Range("C12").Value
      rng.Offset(, 8).Value = wsForm.Range("C18").Value
      rng.Offset(, 9).Value = wsForm.Range("F7").Value
      rng.Offset(, 10).Value = wsForm.Range("F8").Value
      rng.Offset(, 11).Value = wsForm.Range("F6").Value
      rng.Offset(, 12).Value = wsForm.Range("F18").Value
      rng.Offset(, 13).Value = wsForm.Range("F9").Value
      rng.Offset(, 14).Value = wsForm.Range("F13").Value
      rng.Offset(, 15).Value = wsForm.Range("F16").Value
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'clear the form[/COLOR]
      wsForm.Range("C4:C18").Value = ""
      wsForm.Range("F6:F18").Value = ""
 
   [COLOR=green]'tidy up[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsForm = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsData = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

If you are going to use a command button on sheet1 to run the code then place the above code in the Sheet1 module.

In edit mode, double click the command button and insert this line :

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdProcess_Click()
   [COLOR=red]ProcessForm[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[tonylpcs@yahoo.co.uk]

Thank you for your help,

worked a treat

Tony