Call Quality Form on Excel with VBA

P

popoyM

New Member
Joined
Sep 21, 2017
Messages
4
I am a newbie in VBA, and i created a form that copies the data into a new sheet once the "Submit" button is clicked. However, instead of just filling up one row every form completion entry, other data fills up the blank cells from the top row. Also, when the "Submit" button is clicked, it slows the file down and shows each data being copied to each cell instead of just staying on the sheet where the form is. Appreciate all the help! This is the code I used:

Code: 
Sub CopyStuff()


Range("C1").Copy
Sheets("EvalLog").Range("AO" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("C2").Copy
Sheets("EvalLog").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("C3").Copy
Sheets("EvalLog").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("C4").Copy
Sheets("EvalLog").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B6").Copy
Sheets("EvalLog").Range("AM" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B9").Copy
Sheets("EvalLog").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B10").Copy
Sheets("EvalLog").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B11").Copy
Sheets("EvalLog").Range("F" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B12").Copy
Sheets("EvalLog").Range("G" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B14").Copy
Sheets("EvalLog").Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B15").Copy
Sheets("EvalLog").Range("I" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B16").Copy
Sheets("EvalLog").Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B17").Copy
Sheets("EvalLog").Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B18").Copy
Sheets("EvalLog").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B19").Copy
Sheets("EvalLog").Range("M" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B21").Copy
Sheets("EvalLog").Range("N" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B22").Copy
Sheets("EvalLog").Range("O" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B23").Copy
Sheets("EvalLog").Range("P" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B24").Copy
Sheets("EvalLog").Range("Q" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B25").Copy
Sheets("EvalLog").Range("R" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B27").Copy
Sheets("EvalLog").Range("S" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B28").Copy
Sheets("EvalLog").Range("T" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("B29").Copy
Sheets("EvalLog").Range("U" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I9").Copy
Sheets("EvalLog").Range("V" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I10").Copy
Sheets("EvalLog").Range("W" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I11").Copy
Sheets("EvalLog").Range("X" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I12").Copy
Sheets("EvalLog").Range("Y" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I15").Copy
Sheets("EvalLog").Range("Z" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I16").Copy
Sheets("EvalLog").Range("AA" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I17").Copy
Sheets("EvalLog").Range("AB" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I18").Copy
Sheets("EvalLog").Range("AC" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I21").Copy
Sheets("EvalLog").Range("AD" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I22").Copy
Sheets("EvalLog").Range("AE" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I23").Copy
Sheets("EvalLog").Range("AF" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I24").Copy
Sheets("EvalLog").Range("AG" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I27").Copy
Sheets("EvalLog").Range("AH" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I28").Copy
Sheets("EvalLog").Range("AI" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I29").Copy
Sheets("EvalLog").Range("AJ" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I30").Copy
Sheets("EvalLog").Range("AK" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Range("I31").Copy
Sheets("EvalLog").Range("AL" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False


End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
It appears you are using a worksheet as your form, give this way a try
Code: 
Sub CopyStuff()

Dim wr As Long          'write row
Dim src As Worksheet    'source worksheet
Dim dest As Worksheet   'destination worksheet

Set src = ActiveSheet
Set dest = Sheets("EvalLog")

Application.ScreenUpdating = False

With dest
    wr = .Range("A" & Rows.Count).End(xlUp).Row + 1
    
    .Range("AO" & wr).Value = src.Range("C1").Value
    .Range("A" & wr).Value = src.Range("C2").Value
    .Range("B" & wr).Value = src.Range("C3").Value
    .Range("C" & wr).Value = src.Range("C4").Value
    'and so on
    'and so on
    'for all the rest
    .Range("AL" & wr).Value = src.Range("I31").Value
End With

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Thank you. I will try this and provide you an update. Yes I am using a worksheet as my form. I wanted to build a form using VBA but I have yet to figure it out, because I'm such a newbie to VBA.

NoSparks said:
It appears you are using a worksheet as your form, give this way a try
Code: 
Sub CopyStuff()

Dim wr As Long          'write row
Dim src As Worksheet    'source worksheet
Dim dest As Worksheet   'destination worksheet

Set src = ActiveSheet
Set dest = Sheets("EvalLog")

Application.ScreenUpdating = False

With dest
    wr = .Range("A" & Rows.Count).End(xlUp).Row + 1
    
    .Range("AO" & wr).Value = src.Range("C1").Value
    .Range("A" & wr).Value = src.Range("C2").Value
    .Range("B" & wr).Value = src.Range("C3").Value
    .Range("C" & wr).Value = src.Range("C4").Value
    'and so on
    'and so on
    'for all the rest
    .Range("AL" & wr).Value = src.Range("I31").Value
End With

Application.ScreenUpdating = True

End Sub
Click to expand...
 
Upvote 0
Hi,

I have tried to use the code that you have provided. When I finished and tested it, I got an error:
open
"400" and Run -Time error '1004': Applicatio-defined or object-defined error.

Here's the link to the file if you want to check it out: https://1drv.ms/x/s!AjXGiUx86RrFgm8uzRUvmL_5XhOB

NoSparks said:
It appears you are using a worksheet as your form, give this way a try
Code: 
Sub CopyStuff()

Dim wr As Long          'write row
Dim src As Worksheet    'source worksheet
Dim dest As Worksheet   'destination worksheet

Set src = ActiveSheet
Set dest = Sheets("EvalLog")

Application.ScreenUpdating = False

With dest
    wr = .Range("A" & Rows.Count).End(xlUp).Row + 1
    
    .Range("AO" & wr).Value = src.Range("C1").Value
    .Range("A" & wr).Value = src.Range("C2").Value
    .Range("B" & wr).Value = src.Range("C3").Value
    .Range("C" & wr).Value = src.Range("C4").Value
    'and so on
    'and so on
    'for all the rest
    .Range("AL" & wr).Value = src.Range("I31").Value
End With

Application.ScreenUpdating = True

End Sub
Click to expand...
 
Upvote 0
Please disregard, the code worked. I just mistyped "I9" to "19" upon further checking. Thank you very much!

popoyM said:
Hi,

I have tried to use the code that you have provided. When I finished and tested it, I got an error:
open
"400" and Run -Time error '1004': Applicatio-defined or object-defined error.

Here's the link to the file if you want to check it out: https://1drv.ms/x/s!AjXGiUx86RrFgm8uzRUvmL_5XhOB
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
183
NdNoviceHlp
NdNoviceHlp
H
  • Solved
Macro to speedy up process when copying Data
Replies
3
Views
76
Logit
Logit
W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
198
who am i
W
W
  • Solved
Add a column w/ formula to copy / paste macro
Replies
5
Views
300
wknight7
W
C
  • Solved
Need Help VBA
2
Replies
10
Views
427
Trebor76
T

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

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
Back
Top