Inserting figires from another sheets to a Form

[lapot]

Dear all
I have an excel document made up from 3 sections; Form, Details and Figures

My form has some boxes that I need to fill in in order to make payments. I have colour coded the boxes to be filled in and these boxes relates to the other sheets. What I like to do is create a macro which creates different forms with a click of a button for individual Payees on the “Figures Sheet” and inserts the data to the relating boxes on the Form .

Here is the boxes links from the Form Sheet ;

IN FIGURES = Sheet (Figures) Colum J
PAYEE: = Sheet (Figures) Colum A
THEIR REF: = Sheet (Figures) Colum E
BANK: = Sheet (Details) Colum D
SORT CODE: = Sheet (Details) Colum E
ACCOUNT NUMBER: = Sheet (Details) Colum F

I have attached the form

Can this be doable?

[jaslake]

Hi lapot

Sharon Hut appears 3 times in "Figures". Should 3 Forms be created for her? If yes, what will you call them (cannot all be named the same)?

[lapot]

Firstly I used Vlookup and Transferred Bank,Sort code and Account number to Figures Sheet. Then I filtered blank cell rows on Colum J Figures Sheet, this left me just names and payment total then I created a temp sheet copied the header Row and the next row with the name and it is datas between A3:AA4 to the temp file , From the temp file I entered the data to the Form's relevant boxes.
My problem is it only crates one form, I like to do as many form for each name on Figures sheet in one go. Can this be done?

here is the new sheet wit two macro on it , one clears and one inserts data on the form.

Thanks in advance

[jaslake]

Hi lapot

Yes, this can be done...

My problem is it only crates one form, I like to do as many form for each name on Figures sheet in one go. Can this be done?

Based on your Sample File you'll have four new Forms created. What do you want to do with the Forms after they're created? Save them to new Worksheets?

[lapot]

We usually print the form and delete it. It also deletes the Temp sheet after the form done and ask me everytime if I want to delete the temp sheet. Can this also be turned off?

Many thanks

[lapot]

Bu the way names some tımes more than 4.

[jaslake]

Let me look at it...

[jaslake]

Hi lapot

Well, I found it easier to simply rewrite the Code...too many selects. So, this Code is in the attached. No Temp Worksheet required.
Open up to Sheet Form...Ctrl + x will fire the Code.

Option Explicit

Sub Fill_Form()
   Dim tWs          As Worksheet
   Dim sWs          As Worksheet
   Dim sWSrng       As Range
   Dim sWScel       As Range
   Dim myCells      As Range

   Set tWs = Sheets("Form")
   Set sWs = Sheets("Figures")

   Application.ScreenUpdating = False
   With sWs
      .Range("Table_Query_from_Partner4[[#Headers],[PAYEE:]]").AutoFilter
      .ListObjects("Table_Query_from_Partner4").Range.AutoFilter Field:=10, Criteria1:="<>"
      Set sWSrng = .ListObjects("Table_Query_from_Partner4").ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

      For Each sWScel In sWSrng
         With tWs
            Set myCells = Union(.Range("F12:M12"), .Range("F13:M13"), .Range("F15:M15"), .Range("F17:M17"), .Range("F19:M19"), .Range("F21:G21"), .Range("M21:M21"))
            myCells.ClearContents
         End With

         tWs.Cells(13, "F").Value = sWScel.Offset(0, 9).Value   'Column J
         tWs.Cells(15, "F").Value = sWScel.Offset(0, 0).Value   'Column A
         tWs.Cells(17, "F").Value = sWScel.Offset(0, 4).Value   'Column E
         tWs.Cells(19, "F").Value = sWScel.Offset(0, 21).Value   'Column Y
         tWs.Cells(21, "F").Value = sWScel.Offset(0, 25).Value   'Column Z
         tWs.Cells(21, "M").Value = sWScel.Offset(0, 26).Value   'Column AA
         tWs.Range("F12").FormulaR1C1 = "=SpellNumber(R[1]C)"

         With Sheets("Form").PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = 1
         End With
         '         ActiveWindow.SelectedSheets.PrintOut Copies:=1
         ActiveSheet.PrintPreview
      Next sWScel
      .Range("Table_Query_from_Partner4[[#Headers],[PAYEE:]]").AutoFilter
   End With
   Application.ScreenUpdating = True
End Sub

To actually Print the Form change this from Preview to Printout.

'         ActiveWindow.SelectedSheets.PrintOut Copies:=1
         ActiveSheet.PrintPreview

[lapot]

Thanks for this Jaslake sorry I couldn't get back to you yesterday because I was off work , your time is greatly appreciated.

Unfortunately It doesn't put the correct total on the form, my payment figure is under Colum J (In Figures), it looks like your total figures is coming from Column U or V which isn't correct.

Colum J is the total of the Total Amount column (Column I), this is what wee need to pay.

[lapot]

I managed sort it out

I have changed this line

 tWs.Cells(19, "F").Value = sWScel.Offset(0, 21).Value   'Column Y

to this

tWs.Cells(19, "F").Value = sWScel.Offset(0, 9).Value   'Column Y

I have one more things to sort it I out. Is there a way if I m paying more than one invoice I can insert all the invoice references which is under Column E (Their ref) on to form Cell F17?

[jaslake]

Hi lapot

I anticipated this issue in my Post #2...guess I should have asked the question differently. It'll take a rewrite of the Code...will get back to you.

Is there a way if I m paying more than one invoice I can insert all the invoice references which is under Column E (Their ref) on to form Cell F17?

[lapot]

Hi Jaslake

did you managed to get some result?

kind regards

[jaslake]

Hi lapot

No I haven't. For some reason the Filter on your Table is not functioning properly and I haven't been able to resolve it.

[lapot]

Thanks but there must be a formula to check column A and if the name is same merge the references(Column Ref) onto column Combined.

I have attached sample sheet

thank you for your time.

[lapot]

I have sorted with these formulas

On column AE this formula break the reference

=IF(Y4<>Y3,AC4,AE3 & "-" & AC4)

with this finial formula I created final references on column AF

=IF(Y4<>Y5,CONCATENATE(,AE4,),"")

I have inserted this line in to macro

 tWs.Cells(17, "F").Value = sWScel.Offset(0, 31).Value   'Column E - Their Ref

worked perfectly

thank you for your time.

[jaslake]

Hi lapot

Glad you got it sorted.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.