Dear,
I would like to use the existing VBA from the Booking sheet directly to the Data Sheets and Admin to write all data to each Sheet.
kindly request,
Edb
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.)
write to 2 sheets with existing VBA
0
Discussion
Hello Erida,
Even after reviewing your file it's not clear what you are wanting to do.
What data / cells on the "Booking" sheet do you want to copy?
Where on the "Data" sheet is the data to be copied? (ex: "Booking"(D7) to "Data"(??) )
Where on the "Admin" sheet is the data to be copied? (ex: "Booking"(D7) to "Admin"(??) )
Your macro "Data_Booking", as written, only adds a new row to the "Data" sheet; nothing is copied. There is a button to trigger the macro on each of the "Booking", "Data", and "List" sheets; in my opinion it should only be on the "Booking" sheet.
We are willing to help but exactly what you want needs to be more clear.
Even after reviewing your file it's not clear what you are wanting to do.
What data / cells on the "Booking" sheet do you want to copy?
Where on the "Data" sheet is the data to be copied? (ex: "Booking"(D7) to "Data"(??) )
Where on the "Admin" sheet is the data to be copied? (ex: "Booking"(D7) to "Admin"(??) )
Your macro "Data_Booking", as written, only adds a new row to the "Data" sheet; nothing is copied. There is a button to trigger the macro on each of the "Booking", "Data", and "List" sheets; in my opinion it should only be on the "Booking" sheet.
We are willing to help but exactly what you want needs to be more clear.
WillieD24 (rep: 557) Oct 8, '23 at 6:01 pm
Hello WillieD24
My question is if I add the line to the Data sheet with the existing macro, the same line is added to the Sheet Admin with the same macro (nothing else needs to be copied). The Booking buttons on the tabs are hyperlinks to the Booking tab.
My question is if I add the line to the Data sheet with the existing macro, the same line is added to the Sheet Admin with the same macro (nothing else needs to be copied). The Booking buttons on the tabs are hyperlinks to the Booking tab.
Edria (rep: 2) Oct 9, '23 at 3:19 am
Edria,
You say "nothing else needs to be copied" yet in your original post you say "write all data to each Sheet". These statements contradict each other. If you do want the "Booking" info copied I have written code for that.
You say "nothing else needs to be copied" yet in your original post you say "write all data to each Sheet". These statements contradict each other. If you do want the "Booking" info copied I have written code for that.
WillieD24 (rep: 557) Oct 12, '23 at 3:44 pm
Answers
0
Selected Answer
I have adjusted the formula so that it now works with what my question was about
Sub data_Booking()
With Sheets("Data").ListObjects(1)
.ListRows.Add
.DataBodyRange.Cells(.ListRows.Count, 1).Resize(, 38) = Array([Q7].Value, [Q11].Value, [Q13].Value, [Q15].Value, [Q17].Value, [Q21].Value, [Q19].Value, [Q23].Value, [D9].Value, [D11].Value, [D13].Value, [D15].Value, [D17].Value, [D19].Value, [D21].Value, [D23].Value, [D25].Value, [D27].Value, [D29].Value, [D31].Value, [k9].Value, [K11].Value, [K13].Value, [K15].Value, [K17].Value, [K19].Value, [K21].Value, [K23].Value, [K25].Value, [K27].Value, [K29].Value, [K31].Value, [Q25].Value, [Q27].Value, [Q29].Value, [Q31].Value, [Q33].Value, [Q35].Value)
End With
With Sheets("Admin").ListObjects(1)
.ListRows.Add
.DataBodyRange.Cells(.ListRows.Count, 1).Resize(, 38) = Array([Q7].Value, [Q11].Value, [Q13].Value, [Q15].Value, [Q17].Value, [Q21].Value, [Q19].Value, [Q23].Value, [D9].Value, [D11].Value, [D13].Value, [D15].Value, [D17].Value, [D19].Value, [D21].Value, [D23].Value, [D25].Value, [D27].Value, [D29].Value, [D31].Value, [k9].Value, [K11].Value, [K13].Value, [K15].Value, [K17].Value, [K19].Value, [K21].Value, [K23].Value, [K25].Value, [K27].Value, [K29].Value, [K31].Value, [Q25].Value, [Q27].Value, [Q29].Value, [Q31].Value, [Q33].Value, [Q35].Value)
End With
t = Range("AA7")
Range("Q9,Q11,Q13,Q15,Q19,Q17,Q21,D9,D11,D13,D15,D17,D19,D21,D23,D25,D27,D29,D31,K9,K11,K13,K15,K17,K19,K21,K21,K23,K25,K27,K29,K31,Q23,Q27,Q25,Q29,Q31,Q33,Q35").ClearContents
' reset booking row
Range("Q44").Value = ""
Range("AA7") = t + 1
End Sub
Discussion
Hello Edria,
The change you have made is the same as what I was going to suggest. I am confused when you say "so now that it works" because the only thing it does is add a new row with borders - no data is copied. (I have tested this solution). I can't help you with that because you have not indicated which data gets copied to which cells. Hyperlinking the buttons is one way to get the job done but it is rarely used. The common method is to directly assign the macro to the button.
But if you are happy with this solution, that is all that matters.
The change you have made is the same as what I was going to suggest. I am confused when you say "so now that it works" because the only thing it does is add a new row with borders - no data is copied. (I have tested this solution). I can't help you with that because you have not indicated which data gets copied to which cells. Hyperlinking the buttons is one way to get the job done but it is rarely used. The common method is to directly assign the macro to the button.
But if you are happy with this solution, that is all that matters.
WillieD24 (rep: 557) Oct 9, '23 at 11:48 am
@Edria Glad you seem to have a solution but, like Willie, I thought to use your data_Booking procedure. I too tried it and saw a new row added to the table but no actual data.
I then saw there was a Selected Answer (so stopped but went to see what Willie had done, assuming he had provided a solution).
I then saw there was a Selected Answer (so stopped but went to see what Willie had done, assuming he had provided a solution).
John_Ru (rep: 6142) Oct 9, '23 at 12:40 pm
@Edria
I have been working on code to copy the data from "Booking" sheet to "Data" and "Admin" sheets. In doing so, I discovered a couple of things with your file. It was created 2020-09-07 by someone else. There is also some hidden code on the "Booking" sheet. If I click in or attempt to change "Q44" or "AA7" code is triggered which locks-up Excel (I am using 2016). Also, if I try to use the mini calendar, hidden code is also triggered which locks-up Excel. In both cases the only way to get out of this lock-up state is to re-boot my laptop which tells me a macro is running.
If you are interested in the code I came up with to copy the info on the "Booking" sheet to both the "Data" & "Admin" sheets, let me know and I will post it.
Cheers :-)
I have been working on code to copy the data from "Booking" sheet to "Data" and "Admin" sheets. In doing so, I discovered a couple of things with your file. It was created 2020-09-07 by someone else. There is also some hidden code on the "Booking" sheet. If I click in or attempt to change "Q44" or "AA7" code is triggered which locks-up Excel (I am using 2016). Also, if I try to use the mini calendar, hidden code is also triggered which locks-up Excel. In both cases the only way to get out of this lock-up state is to re-boot my laptop which tells me a macro is running.
If you are interested in the code I came up with to copy the info on the "Booking" sheet to both the "Data" & "Admin" sheets, let me know and I will post it.
Cheers :-)
WillieD24 (rep: 557) Oct 11, '23 at 12:55 pm
Dear Willie,
Since you put some time into it, thank you for all your efforts.
Ed, if you wrote the code, I would like to see it.
Where is the hidden code on the Booking page? I can't find it?
Kind regards Edb
Edria (rep: 2) Oct 13, '23 at 9:24 am
0
@Edria
As for the hidden code I can't help you with that because I too could not find it. If this was my file I would make a completely new "Booking" sheet, but that's just me.
I have attached my revision of your file so you can review the code. On the "Booking" sheet I updated the button and assigned my new macro (Data_Booking_2) to it. (the original macro assigned wouldn't run). I also updated the buttons on the "Data" and "List" sheets; removed the hyperlink and assigned the macro directly to the buttons. On the "Data" and "Admin" sheets you will see a number of tests I did.
The code which would clear the "Booking" sheet is commented out. If you choose to use this code un-comment the ClearContents code of your choice.
Cheers :-)