Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Entering Form Data into Two Different Sheets

0

Hello again.

I am trying to wrap my head around how to make this happen and if I can do so by adjusting my current working macro.

Iam trying to figure out how to change my macro so that when I enter my form data and hit submit that it deposits the data into two identical worksheets.  

The name of my second datasheet would be "ArchiveTO"

Thank you for looking.

Sub Store_Data()
' Takes data from one worksheet and stores in the next empty row on another Worksheet
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Long
' Make some sheet variables so we can use those instead of hard-coding sheet reference
Set sourceSheet = Sheets("Request")
Set dataSheet = Sheets("TimeOff")
' Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("B" & Rows.Count).End(xlUp).Offset(1).Row
'Input the form values into the Data Sheet.
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C4").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F4").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("C6").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F6").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("C8").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F8").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("C10").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("C12").Value
'Clear Data
sourceSheet.Range("C4").Value = ""
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("C6").Value = ""
sourceSheet.Range("F6").Value = ""
sourceSheet.Range("C8").Value = ""
sourceSheet.Range("F8").Value = ""
sourceSheet.Range("C10").Value = ""
sourceSheet.Range("C12").Value = ""
End Sub
excel
posted by: Galanthias (rep: 4) on Tue Jul 23, 2024 at 2:48 pm
Answer
Discuss

Discussion

@Galanthias
A couple of suggestions for future posts:
As John has recommended, in the upper right corner click on the blue "Rules" menu and review what is expected.
Also as John has recommended (and I do too) attach a sample file with the code so we can see how things are (or are not) working.
When posting your code, click "CODE" at the top. Then remove the text "Code_Goes_Here" between the [CODE] tags and paste in your code. It will then look like the code John and I have posted and be easier to read.
Glad we have been able to help you. Come back anytime.
Cheers   :-)
WillieD24 (rep: 587) Jul 23, '24 at 7:31 pm
@Galanthias,
If you want to ensure all 8 cells have something in them you could add some code at the start of your macro to check for this:
If Range("C4") = "" Or Range("F4") = "" Or Range("C6") = "" Or Range("F6") = "" _
    Or Range("C8") = "" Or Range("F8") = "" Or Range("C10") = "" Or Range("C12") = "" Then
    MsgBox "    Some data is missing."
    Exit Sub
End If

:-)
WillieD24 (rep: 587) Jul 23, '24 at 10:42 pm
My apologies.  I'm an old dog trying to remember my old tricks.
Galanthias (rep: 4) Jul 24, '24 at 8:59 am
Add to Discussion

Answers

0
Selected Answer

Hi again Galanthias

As we said before, it really helps us (to help you) if you attach a representative Excel file with your question.

That said, here's the simplest way to copy the data to a second sheet, just repeating the "write code" and redefining the variable dataSheet (there are better ways!), See changes in bold:

Sub Store_Data()

' Takes data from one worksheet and stores in the next empty row on another Worksheet

Dim sourceSheet As Worksheet

Dim dataSheet As Worksheet

Dim nextRow As Long

' Make some sheet variables so we can use those instead of hard-coding sheet reference

Set sourceSheet = Sheets("Request")

Set dataSheet = Sheets("TimeOff")

' Get the next empty row from the first Data sheet.

nextRow = dataSheet.Range("B" & Rows.Count).End(xlUp).Offset(1).Row

'Input the form values into the Data Sheet.

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C4").Value

dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F4").Value

dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("C6").Value

dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F6").Value

dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("C8").Value

dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F8").Value

dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("C10").Value

dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("C12").Value
' now set the varaible to the SECOND sheet...
Set dataSheet = Sheets("ArchiveTO")

' Get the next empty row from the SECOND Data sheet.

nextRow = dataSheet.Range("B" & Rows.Count).End(xlUp).Offset(1).Row

'Input the form values into the SECOND Data Sheet.

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C4").Value

dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F4").Value

dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("C6").Value

dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F6").Value

dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("C8").Value

dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F8").Value

dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("C10").Value

dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("C12").Value

'Clear Data

sourceSheet.Range("C4").Value = ""

sourceSheet.Range("F4").Value = ""

sourceSheet.Range("C6").Value = ""

sourceSheet.Range("F6").Value = ""

sourceSheet.Range("C8").Value = ""

sourceSheet.Range("F8").Value = ""

sourceSheet.Range("C10").Value = ""

sourceSheet.Range("C12").Value = ""

End Sub

Correction #1 dated 23 July 2024

Above code changed to make it:

' now set the varaible to the SECOND sheet...
Set dataSheet = Sheets("ArchiveTO")

Hope this makes sense to you. If so, please remember to mark this Answer as Selected.

Also, kindly look at the Rules of the Forum (blue hyperlink above and to the right of your question) so you can prepare future questions in a way that helps everyone. Thanks.

posted by: John_Ru (rep: 6417) on Tue Jul 23, 2024 at 3:17 pm
Discuss

Discussion

A separate variable is needed for the "ArchiveTO" sheet and this would be used in the repeat of the copy code (archiveSheet instead of dataSheet)
Dim archiveSheet As Worksheet
Set archiveSheet = Sheets("ArchiveTO")

Also, the code to clear the source sheet cells can be written in one line:
sourceSheet.Range("C4, F4, C6, F6, C8, F8, C10, C12").Value = ""

Cheers   :-)
WillieD24 (rep: 587) Jul 23, '24 at 3:46 pm
@Willie- that new variable might be clearer but is unnecesaary; in such simple code you can just re-use a variable. Like you, I know how to write to a range but I get the impression Galanthias is just starting with VBA so I think it's better not to overwhelm her(?) so early on. 
John_Ru (rep: 6417) Jul 23, '24 at 4:07 pm
@John,
Agreed, but in your code for the second sheet the variable is still set to "TimeOff" so the data is being copied to the same sheet. To reuse it, it needs to be set to "ArchiveTO" to copy the data to a second sheet.
:-)
WillieD24 (rep: 587) Jul 23, '24 at 5:11 pm
@Willie - oops! I forgot to do that but have corrected it now. Thanks for catching that 
John_Ru (rep: 6417) Jul 23, '24 at 5:19 pm
Many thanks, this puts everything into percpective too. 
Galanthias (rep: 4) Jul 24, '24 at 9:23 am
Glad that helped, Galanthias. Thanks for selecting my Answer. 
John_Ru (rep: 6417) Jul 24, '24 at 9:48 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login