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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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

A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
750
Joe4
Joe4
L
  • Solved
VBA Error
2 3
Replies
29
Views
350
Joe4
Joe4
N
  • Solved
VBA - Increment range and loop until blank Cell
Replies
3
Views
141
dreid1011
dreid1011
M
  • Question
Table issue with VBA
Replies
0
Views
86
Mike2
M
K
  • Question
Paste Data Below last row
Replies
3
Views
257
rlv01
R

Forum statistics

Threads
1,215,339
Messages
6,124,370
Members
449,155
Latest member
ravioli44

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