Form + overview

[EmilyNL]

Hello

We are building a mini database of all the claims we file with our carriers. The idea is that we fill in all the details in one tab called 'details' (the standard form that we can copy/paste from other spreadsheets also). Once completed, we run a macro after pressing a button or some command to pull all the data into another tab called 'overview'. Then over time, we will end up with an overview of all the claims we filed.

The only problem is: I have no clue how to write a macro for this.

I have an excel file which contains:
Tab 'Details'
Tab 'Overview'

Tab detail contains:
C4 - date of claim (map to tab overview - first available row in column F)
C5 - shipping date (map to tab overview - first available row in column E)
C6 - Company name
C7 - Company address
C8 - Post code/city
C9 - Country
C10 - Service provider (map to tab overview - first available row in column A)
C11 - Part numbers
C12 - Description
C13 - Order number (map to tab overview - first available row in column C)
C14 - Invoice number (map to tab overview - first available row in column B)
C15 - Claim number (map to tab overview - first available row in column D)
C16 - Weight (kg)
C17 - Price (map to tab overview - first available row in column G)
C18 - Complaint
C19 - Credit (map to tab overview - first available row in column H)
C20 - Total loss for this claim (map to tab overview - first available row in column J)

Thanks in advance!

 

[cooper645]

here you go

Option Explicit


Sub x1092845x()
'https://www.mrexcel.com/forum/excel-questions/1092845-form-overview.html


Dim i As Double, ds As Worksheet, os As Worksheet


Set ds = Sheets("Details")
Set os = Sheets("Overview")
i = os.Cells(Rows.Count, "A").End(xlUp).Row + 1


os.Cells(i, 1).Value = ds.Range("C10") 'Col A - Service Provider
os.Cells(i, 2).Value = ds.Range("C14") 'Col B - Invoice Number
os.Cells(i, 3).Value = ds.Range("C13") 'Col C - Order Number
os.Cells(i, 4).Value = ds.Range("C15") 'Col D - Claim Number
os.Cells(i, 5).Value = ds.Range("C5") 'Col E - Shipping Date
os.Cells(i, 6).Value = ds.Range("C4") 'Col F - Date of claim
os.Cells(i, 7).Value = ds.Range("C17") 'Col G - Price
os.Cells(i, 8).Value = ds.Range("C19") 'Col H - Credit
os.Cells(i, 9).Value = ds.Range("C18") 'Col I - Complaint
os.Cells(i, 10).Value = ds.Range("C20") 'Col J - Total Loss
os.Cells(i, 11).Value = ds.Range("C6") 'Col K - Company Name
os.Cells(i, 12).Value = ds.Range("C7") 'Col L - Company Address
os.Cells(i, 13).Value = ds.Range("C8") 'Col M - Post Code/City
os.Cells(i, 14).Value = ds.Range("C9") 'Col N - Country
os.Cells(i, 15).Value = ds.Range("C11") 'Col O - Part Numbers
os.Cells(i, 16).Value = ds.Range("C12") 'Col P - Description
os.Cells(i, 17).Value = ds.Range("C16") 'Col Q - Weight


'Optional Code to empty the details range (If you wish to use just reomve the apostophe at the start below)


'ds.Range("C4:C20").ClearContents


End Sub

 

[Fluff]

Another way

Sub EmilyNL()
   Dim x
   With Application
   x = .Index(.Transpose(Sheets("Details").Range("C1:C20").Value), 1, Array(10, 14, 13, 15, 5, 4, 17, 19, 0, 20))
   End With
   Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = x
End Sub

 

[EmilyNL]

here you go

Option Explicit


Sub x1092845x()
'https://www.mrexcel.com/forum/excel-questions/1092845-form-overview.html


Dim i As Double, ds As Worksheet, os As Worksheet


Set ds = Sheets("Details")
Set os = Sheets("Overview")
i = os.Cells(Rows.Count, "A").End(xlUp).Row + 1


os.Cells(i, 1).Value = ds.Range("C10") 'Col A - Service Provider
os.Cells(i, 2).Value = ds.Range("C14") 'Col B - Invoice Number
os.Cells(i, 3).Value = ds.Range("C13") 'Col C - Order Number
os.Cells(i, 4).Value = ds.Range("C15") 'Col D - Claim Number
os.Cells(i, 5).Value = ds.Range("C5") 'Col E - Shipping Date
os.Cells(i, 6).Value = ds.Range("C4") 'Col F - Date of claim
os.Cells(i, 7).Value = ds.Range("C17") 'Col G - Price
os.Cells(i, 8).Value = ds.Range("C19") 'Col H - Credit
os.Cells(i, 9).Value = ds.Range("C18") 'Col I - Complaint
os.Cells(i, 10).Value = ds.Range("C20") 'Col J - Total Loss
os.Cells(i, 11).Value = ds.Range("C6") 'Col K - Company Name
os.Cells(i, 12).Value = ds.Range("C7") 'Col L - Company Address
os.Cells(i, 13).Value = ds.Range("C8") 'Col M - Post Code/City
os.Cells(i, 14).Value = ds.Range("C9") 'Col N - Country
os.Cells(i, 15).Value = ds.Range("C11") 'Col O - Part Numbers
os.Cells(i, 16).Value = ds.Range("C12") 'Col P - Description
os.Cells(i, 17).Value = ds.Range("C16") 'Col Q - Weight


'Optional Code to empty the details range (If you wish to use just reomve the apostophe at the start below)


'ds.Range("C4:C20").ClearContents


End Sub

Thank you!

I checked both your idea and MrFluff but this one I got sorted really quick!

 

[EmilyNL]

Bonus request... Is it possible to add a button to start the macro? My colleague who will be working with this does not have the developer tools installed.

 

[cooper645]

You could just insert a shape and then right click on it and assign the macro.