create a module that copies info from one sheet to another

[geno32080]

Hello all.
I'm trying to figure out how I can have info transposed from one sheet to another without having to click a button.
When the user fills in the form (Source Sheet12) I need those select cells to be filled into select cells on the (destination Sheet7) Once the info Form is entered, saved and emailed, the form gets cleared and the process starts over. I need the info that was transposed onto the summary sheet (Sheet7) to remain, as its a cumulative summary sheet that runs for a year.
I was thinking a module? I would need it to initialize, but I'm not that good at the dim codes.

COPY source Sheet12 Cell B3 to destination Sheet7 next available row starting with B6
COPY source Sheet12 Cell C17 to destination Sheet7 next available row starting with C6
COPY source Sheet12 Cell C14 to destination Sheet7 next available row starting with D6

I need help writing the code to perform this. Anyone?

I always get the best help here, I thank everyone.

 

[gordsky]

you could use a worksheet change event on sheet12 to then update the lastrow of sheet7. You say FORM in your pst. Are you using a userform or a sheet cells made to look like a form

 

[geno32080]

you could use a worksheet change event on sheet12 to then update the lastrow of sheet7. You say FORM in your pst. Are you using a userform or a sheet cells made to look like a form

Hi, the term form refers to an Absence Form, its on a excel sheet that is Sheet12. The Absence Form is filled out by the employee to request time off. The destination sheet (Sheet7) also a excel sheet is a summary sheet of the Absence Form. B3 = Date Requested Off ,C17 = Reason and C14 =Amount of Time Requested off. If I can get excel to copy the info from those cells to the Summary sheet, using the next available row on the summary sheet, It will save time and accuracy for management. I would ideally like to accomplish this without having to click a button. I tried the =Sheet!12("B3") idea, but it got messy, especially when the Absence Form was cleared. It would also clear the info on the Summary Sheet. If I need a button, I could include a script in the VBA that emails the Absence Form.
Thanks for looking at this.

 

[gordsky]

Use a worksheet_change event in the Sheet12 code. I am guessing the reason it also cleared the destination sheet when you cleared the form was because you hadnt disable events meaning that when the form cleared it also triggered another worksheet_change event and then pasted the empty value back to the destiantion sheet.

Maybe something like this?

Option Compare Text
Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lrow As Long
Dim Cpy As Boolean

Set ws1 = Sheets("Sheet1") ' change to be your source sheet name
Set ws2 = Sheets("Sheet2") ' change to be your destination sheet name
lrow = ws2.Cells(Rows.count, 2).End(xlUp).row

    Application.EnableEvents = False

If Not Intersect(target, ws1.Range("B3")) Is Nothing Then
    If ws1.Range("C14") = "" Or ws1.Range("C17") = "" Then
        Cpy = False
    Else
        Cpy = True
    End If
    
    ElseIf Not Intersect(target, ws1.Range("C14")) Is Nothing Then
        If ws1.Range("B3") = "" Or ws1.Range("C17") = "" Then
            Cpy = False
            Else
                Cpy = True
            End If

        ElseIf Not Intersect(target, ws1.Range("C17")) Is Nothing Then
            If ws1.Range("B3") = "" Or ws1.Range("C14") = "" Then
                Cpy = False
            Else
                Cpy = True
            End If
            
            
Else
    Cpy = False
End If

Select Case Cpy
    Case True:
        ws2.Range("B" & lrow + 1) = ws1.Range("B3")
        ws2.Range("D" & lrow + 1) = ws1.Range("C14")
        ws2.Range("C" & lrow + 1) = ws1.Range("C17")
        ws1.Range("B3").ClearContents
        ws1.Range("C14").ClearContents
        ws1.Range("C17").ClearContents
    Case False:
End Select

Application.EnableEvents = True


End Sub

 

[geno32080]

Use a worksheet_change event in the Sheet12 code. I am guessing the reason it also cleared the destination sheet when you cleared the form was because you hadnt disable events meaning that when the form cleared it also triggered another worksheet_change event and then pasted the empty value back to the destiantion sheet.

Maybe something like this?

[/QUOTE]
[QUOTE="gordsky, post: 5853389, member: 373896"]
Option Compare Text
Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lrow As Long
Dim Cpy As Boolean

Set ws1 = Sheets("Sheet1") ' change to be your source sheet name
Set ws2 = Sheets("Sheet2") ' change to be your destination sheet name
lrow = ws2.Cells(Rows.count, 2).End(xlUp).row

    Application.EnableEvents = False

If Not Intersect(target, ws1.Range("B3")) Is Nothing Then
    If ws1.Range("C14") = "" Or ws1.Range("C17") = "" Then
        Cpy = False
    Else
        Cpy = True
    End If
   
    ElseIf Not Intersect(target, ws1.Range("C14")) Is Nothing Then
        If ws1.Range("B3") = "" Or ws1.Range("C17") = "" Then
            Cpy = False
            Else
                Cpy = True
            End If

        ElseIf Not Intersect(target, ws1.Range("C17")) Is Nothing Then
            If ws1.Range("B3") = "" Or ws1.Range("C14") = "" Then
                Cpy = False
            Else
                Cpy = True
            End If
           
           
Else
    Cpy = False
End If

Select Case Cpy
    Case True:
        ws2.Range("B" & lrow + 1) = ws1.Range("B3")
        ws2.Range("D" & lrow + 1) = ws1.Range("C14")
        ws2.Range("C" & lrow + 1) = ws1.Range("C17")
        ws1.Range("B3").ClearContents
        ws1.Range("C14").ClearContents
        ws1.Range("C17").ClearContents
    Case False:
End Select

Application.EnableEvents = True


End Sub

Seems like it should work, do we want this in a module or in the sheet12 or sheet7? It has some conflict in the sheets as I have code this in both sheets.
Keeps employees from messing up the form. If I need to drop it I can.

Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:K25"
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If (Intersect(target, Range("B6:D22")) Is Nothing) Then
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
End If
End Sub

 

[gordsky]

you need to put my code in the sheet12 module. (if thats where your data is entered)
I dont think you will need to remove your other code as
Activate doesnt cause a worksheetchange event
Worksheet_SelectionChange triggers when a different cell is selected (not when the info is changed)

My code will trigger when code is changed so i dont think it will conflict

 

[geno32080]

you need to put my code in the sheet12 module. (if thats where your data is entered)
I dont think you will need to remove your other code as
Activate doesnt cause a worksheetchange event
Worksheet_SelectionChange triggers when a different cell is selected (not when the info is changed)

My code will trigger when code is changed so i dont think it will conflict

Thanks, I pasted it to the sheet12, changed the sheets names,. Have a little more modifying to get the date to come over but all in all Good Job! Thanks !
I always get the best results from this site, so many good people here.

 

[gordsky]

glad i could help

 

[geno32080]

glad i could help

One more thing,, The Date on Cell B3 on the source page is populated with a =now() code. To get the info to populate on the summary page I have to click on the B3 cell, it clears the info on the Source page and populates it to the summary page, but then the =now() code is gone? Any ideas as a work around, like a paste special or something so it doesn't delete the =now() code?

 

[gordsky]

One more thing,, The Date on Cell B3 on the source page is populated with a =now() code. To get the info to populate on the summary page I have to click on the B3 cell, it clears the info on the Source page and populates it to the summary page, but then the =now() code is gone? Any ideas as a work around, like a paste special or something so it doesn't delete the =now() code?

you asked for those cells to be cleared so the code does that.

If B3 info contains now() then you dont actually need B3. just have the vbacode reflect now once c14 & c17 have been populated
ie

ws2.Range("B" & lrow + 1) = now()

what triggers the time change in B3 though?? or what populates it?