PDA

View Full Version : VBA Newbie (Trying to write a code to copy and paste info from one sheet to another)



Forum2000s
05-04-2017, 07:53 PM
Hello all,

I'm new to using VBA but I've been trying to learn because I need to create a QA Call Monitoring form for a call center; the workbook needs to have a database sheet. I need the information in the cells on the QAForm sheet, to copy to the QADatabase sheet, and clear the QA form after. Can someone take a look and tell me what I am doing wrong?

I would really appreciate any input to help get this to work.

I'm using Excel 2010.


This is what I've entered:

Sub

erw = QADatabase.Cells(1, 1).CurrentRegion.Rows.Count + 1

QADatabase.Cells(erw, 1) = Range("B3")
QADatabase.Cells(erw, 2) = Range("C3")
QADatabase.Cells(erw, 3) = Range("D3")
QADatabase.Cells(erw, 4) = Range("E3")
QADatabase.Cells(erw, 5) = Range("F3")
QADatabase.Cells(erw, 6) = Range("G3")
QADatabase.Cells(erw, 7) = Range("L3")
End Sub

Leith Ross
05-04-2017, 09:27 PM
Hello Forum2000s,

Here is the working code. Delete the Sub you have in the worksheet "QAForm" module and paste this code in.



Private Sub CommandButton1_Click()


Dim erw As Long

With Worksheets("QADataBase")
erw = .Cells(1, 1).CurrentRegion.Rows.Count + 1
.Cells(erw, 1) = Me.Range("B3") ' Monitor Date
.Cells(erw, 3) = Me.Range("C3") ' Call Date
.Cells(erw, 4) = Me.Range("D3") ' Call Time
.Cells(erw, 5) = Me.Range("E3") ' Campaign
.Cells(erw, 2) = Me.Range("F3") ' Supervisor
.Cells(erw, 6) = Me.Range("G3") ' Agent
.Cells(erw, 7) = Me.Range("H3") ' Score
.Cells(erw, 8) = Me.Range("L3") ' Notes
End With


Me.Range("B3:H3").ClearContents
Me.Range("L3").MergeArea.ClearContents
Me.Range("L11,L16,L21,L26,L31,L36,L51,L56,L61,L66").ClearContents


End Sub

Forum2000s
05-05-2017, 02:31 AM
Hello Forum2000s,

Here is the working code. Delete the Sub you have in the worksheet "QAForm" module and paste this code in.



Private Sub CommandButton1_Click()


Dim erw As Long

With Worksheets("QADataBase")
erw = .Cells(1, 1).CurrentRegion.Rows.Count + 1
.Cells(erw, 1) = Me.Range("B3") ' Monitor Date
.Cells(erw, 3) = Me.Range("C3") ' Call Date
.Cells(erw, 4) = Me.Range("D3") ' Call Time
.Cells(erw, 5) = Me.Range("E3") ' Campaign
.Cells(erw, 2) = Me.Range("F3") ' Supervisor
.Cells(erw, 6) = Me.Range("G3") ' Agent
.Cells(erw, 7) = Me.Range("H3") ' Score
.Cells(erw, 8) = Me.Range("L3") ' Notes
End With


Me.Range("B3:H3").ClearContents
Me.Range("L3").MergeArea.ClearContents
Me.Range("L11,L16,L21,L26,L31,L36,L51,L56,L61,L66").ClearContents


End Sub




Awsome! I was so frustrated with this; do you know of any online resourses that can help me get the basics of the language down?


Thank you for the help!