pixel1987

New Member
Joined
Mar 16, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
ok to start off with ,, thank for taking the time here to read this

ive litterally been learning vla for 24-48 hours and im stuck with a few things i wondering if i could pick your brains to help


soo.. ive attached a spreadsheet where im wanting to add a new line after pasting to my database .

currently i have a macro (macro recorder) to copy the data to the database, delete the form and have a pop up message.

though... if i submitted the form again .. it would re write over the old data.. im just lost where should i put the code and what ever i try just end up me wasting another 2-3 hours figuring out solutions. (its like im passed my cars drivers test and about to drive a plane) so bamboozled on some things. ive uploads pictures and file if anyone can help .. also my code .

also if its not too much trouble .. theres an issue im having with vlook up.. im wanting to from the dropdown box select an option then automatically on the right side where the 5! are.. would like information to pop up from a separate table to show up.

suprised i got this. far but please any help would be great.. of course i would be happy to buy a coffee. (buymeacoffee.com) for anyone helping :)

im on here glued to the desk all day so ill be able to reply straight away

((PS WHAT I UPLOADED AS A QUICK DRAFT OF WHATS ON MY WORK COMPUTER.. IM NOT ABLE TO TRANSFER FILES OUT OF IT,AND VPN LOCKED OUT ON YOUR WEBSITE..SO APOLOGIES FOR THE BASICNESS BUT ILL UPLOAD ALSO WHAT IT MENT TO LOOK LIKE ALSO)


VBA Code:
Sub Macro2()


Set form = ThisWorkbook.Sheets("form")


'MACRO RECORDER COPYING CELLS FROM FORM TO DATABASE IN ROWS (PASTING AS FORMULA) Data come out better this way..



    Range("D7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("E9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("F9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("G9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("H9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("I9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("J9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("K9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("L9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("M9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("N9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("O9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D32").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("P9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FORM").Select
    Range("D35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("DATABASE").Select
    Range("Q9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A29").Select
    Sheets("FORM").Select
    Application.CutCopyMode = False

'CLEARING THE CELLS AFTER TRANSFER

form.Range("d7, d9, d11, d15, d18, d20, d22, d23, d25, d26, d29, d32, d35").Value = ""

'MESSAGE BOX CONFIRMATION

MsgBox "all done"


End Sub




SSSSSS.jpgIMG_5770.jpgDRAFT FORM.jpg
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pixel1987

New Member
Joined
Mar 16, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
lll
 

Attachments

  • DRAFT DATABASEp.jpg
    DRAFT DATABASEp.jpg
    29.8 KB · Views: 4

pixel1987

New Member
Joined
Mar 16, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS

just for people .. this will sort it out
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,329
Messages
5,641,545
Members
417,219
Latest member
FrancoisT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top