Hello All,
I am trying to get a form (worksheet2) to copy the same data into 2 separate worksheets using VBA in Excel. I have the form copying the entries into one worksheet already but am having trouble setting the second worksheet in the VBA Code. Can someone help me? The Data Tab stores the data in a table that cannot have any data manipulation for auditing purposes, so I need to be able to push the same data to a second worksheet (Usable Data Tab) where new columns and data can be added to it by other departments. I have the VBA Code below.
I appreciate any help I can get with this issue. Thank you...
____________________________________________________________________________________________________
Private Sub CommandButton1_Click()
'Make and set variables for the Form & Data Worksheets
Dim FRM As Worksheet, DTA As Worksheet
Set DTA = Sheet1
Set UDTA = Sheet5
Set FRM = Sheet2
'Make and set variables for each cell in the form sheet
Dim EmployeeName As Range, MetricsDate As Range, ActiveJobsPosted As Range, Submissions As Range, Outreach As Range, NewBusinessPartners As Range, NewCandidates As Range, InPersonEvents As Range, SocialMediaMarketingFlyer As Range, Hires As Range, Assists As Range
Dim JobOffersDeclined As Range, ConversionRate As Range, MonthCycle As Range, Months As Range, Years As Range, BobComments As Range, ArneComments As Range
Set EmployeeName = FRM.Range("C3")
Set MetricsDate = FRM.Range("N3")
Set ActiveJobsPosted = FRM.Range("D6")
Set Submissions = FRM.Range("D7")
Set Outreach = FRM.Range("D8")
Set NewBusinessPartners = FRM.Range("D9")
Set NewCandidates = FRM.Range("D10")
Set InPersonEvents = FRM.Range("D11")
Set SocialMediaMarketingFlyer = FRM.Range("D12")
Set Hires = FRM.Range("D13")
Set Assists = FRM.Range("D14")
Set JobOffersDeclined = FRM.Range("D15")
Set ConversionRate = FRM.Range("D16")
Set MonthCycle = FRM.Range("F3")
Set Months = FRM.Range("I3")
Set Years = FRM.Range("L3")
Set BobComments = FRM.Range("E19")
Set ArneComments = FRM.Range("H19")
'Make a variable for the paste cell in the Data worksheet
Dim DestCell As Range
If DTA.Range("A3") = "" Then 'If A3 in the Data Form is empty
Set DestCell = DTA.Range("A3") '...then use A3 as the new destination cell for new entry
Else
Set DestCell = DTA.Range("A3").End(xlDown).Offset(1, 0) 'if row is already populated then use the next empty row
End If
'If no name entered exit the macro
If EmployeeName = "" Then
MsgBox "You must enter a VSC's Name before submitting"
Exit Sub
'End If
'Copy and Paste data from Form into the Data worksheet
EmployeeName.Copy DestCell
MetricsDate.Copy DestCell.Offset(0, 1)
ActiveJobsPosted.Copy DestCell.Offset(0, 2)
Submissions.Copy DestCell.Offset(0, 3)
Outreach.Copy DestCell.Offset(0, 4)
NewBusinessPartners.Copy DestCell.Offset(0, 5)
NewCandidates.Copy DestCell.Offset(0, 6)
InPersonEvents.Copy DestCell.Offset(0, 7)
SocialMediaMarketingFlyer.Copy DestCell.Offset(0, 8)
Hires.Copy DestCell.Offset(0, 9)
Assists.Copy DestCell.Offset(0, 10)
JobOffersDeclined.Copy DestCell.Offset(0, 11)
ConversionRate.Copy DestCell.Offset(0, 12)
MonthCycle.Copy DestCell.Offset(0, 13)
Months.Copy DestCell.Offset(0, 14)
Years.Copy DestCell.Offset(0, 15)
BobComments.Copy DestCell.Offset(0, 16)
ArneComments.Copy DestCell.Offset(0, 17)
'Clear the contents in the Performance Metrics Form
EmployeeName.ClearContents
MetricsDate.ClearContents
ActiveJobsPosted.ClearContents
Submissions.ClearContents
Outreach.ClearContents
NewBusinessPartners.ClearContents
NewCandidates.ClearContents
InPersonEvents.ClearContents
SocialMediaMarketingFlyer.ClearContents
Hires.ClearContents
Assists.ClearContents
JobOffersDeclined.ClearContents
ConversionRate.ClearContents
MonthCycle.ClearContents
Months.ClearContents
Years.ClearContents
BobComments.ClearContents
ArneComments.ClearContents
End Sub
