Report

How to populate information on multiple sheets at once [Solved]

Ask a question JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - Last answered on May 2, 2017 at 11:00 AM by TrowaD
Hi all,

I hope someone can help me figure this out. I have been imputing information on multiple sheets in an excel workbook for quite a while now and i'm certain there's an easier way.

I found out that using the free office homework template would help me plan, and manage all of the bids that we submit and also track our bid to award ratio overall and for different clients. What i would like to do is have all of the information that i enter in the 3 different columns in the bid list sheet populate in the 3 related columns in the other sheet. I hope i'm explaining this well enough. I'm rather new to the site and Excel.

See screenshot for better understanding..https://www.dropbox.com/s/0rnmxo536iuulk6/Mastersheet%20screenshot.pdf?dl=0

Thank you for your help in advance.
Helpful
+0
plus moins
Hi JPSpark,

The link doesn't work.

The code would look something like this :
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
    Target.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
    Target.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
If Not Intersect(Target, Columns("C:C")) Is Nothing Then
    Target.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub


When you confirm a value in column A of your main sheet, then that value will be copied to Sheet2 the first available row in column A.
Column B will be copied to Sheet3 and column C will be copied to Sheet4.
Find those reference in the code and change them to match your scenario.

To use the code: Right-click your main sheets tab and select View Code. Paste the code in the big white field.
JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - Jan 3, 2017 at 04:42 PM
Thank you for the help TrowaD. Much appreciated! I'll give it a go and see how it works.

This Link should work.......

https://www.dropbox.com/s/bibikpoql8u9qwt/Mastersheet.pdf?dl=0
Reply
JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - Jan 4, 2017 at 10:06 PM
I tried the above but i was unable to get it to work not sure if i'm doing it correctly.
Reply
Leave a comment
Helpful
+0
plus moins
Hi JPSpark,

Sorry for the late reply.

If you are still in need of assistance then re-upload your file for me to look at.

Best regards,
Trowa
Leave a comment
Helpful
+0
plus moins
Hi JPSpark,

OK, I got your file now.

So if I understand correctly you want to copy a new entry in columns B:D on sheet "Bid List" and paste it to columns D:F on sheet "TO Sheet". And you want this to happen automatically.

With the code below implemented, when you confirm your data in colmn D on sheet "Bid List", columns B:D of that row will be pasted to the "TO Sheet" first available row based on column D.

To implement the code below, right-click the "Bid List" sheets tab and select "View code". Paste the code in the big white field.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub
Range(Cells(Target.Row, "B"), Cells(Target.Row, "D")).Copy _
Sheets("TO Sheet").Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


Best regards,
Trowa
JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - Feb 13, 2017 at 11:47 AM
Hi Trowa,

Thank you so much for your help. I applied the code as you instructed and it works. After applying the code I realized that the way I handle the Master sheet I would need to make the TO Sheet a separate sheet outside of the workbook. How can I edit this code to make it work how i need it?

Thank you so much in advance.
Reply
TrowaD 2282Posts Sunday September 12, 2010Registration date ModeratorStatus October 19, 2017 Last seen - Feb 14, 2017 at 12:07 PM
Hi JPSpark,

You want to remove the TO Sheet from your workbook and create a new workbook just for that single sheet. What could be the reason for that?

Keeping all sheets together in one workbook makes processing data easier and faster.

Do enlighten me.

Best regards,
Trowa
Reply
JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - Mar 1, 2017 at 07:40 AM
Thank you for your response and I couldn't agree with you more. It is probably my lack of knowledge of excel that prompted me to this method. To make changes to the workbook I have it prompting for a password. I'm the only one that can make changes to the book and my colleagues can only view it in read-only. The only page anyone can make changes to is the TO sheet which is in the workbook you saw, but I currently have it separated as a separate sheet. As I'm typing this I'm remembering reading somewhere a way to have just that one page unlocked. Maybe you can shed some light into this.


Thanks again for all of your help.
Reply
Leave a comment
Helpful
+0
plus moins
Hi JPSpark,

Next to the option to protect your workbook, there is also the option to protect a single sheet (or right-click the sheets tab to find the option as well). So disable the workbook protection and enable the worksheet protection.

To make it more clear which sheet your colleagues are suppose to use, consider this piece of code. It will hide all sheets, except for the TO sheet, when you close the workbook. When you open the workbook, you can right-click the sheets tab to unhide a specific sheet and alter it. When a colleague opens the workbook only the TO sheet is visible. Could be a nice addition.

The code needs to be placed under ThisWorkbook (see pic). Here is the code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "TO Sheet" Then
        ws.Visible = False
    End If
Next ws
End Sub




Best regards,
Trowa
JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - Mar 20, 2017 at 02:02 PM
I just noticed that I was working off an updated version of the sheet I submitted to you and not the old one you provided the code for. Let me retry this and I'll let you know how I make out.

thanks again
Reply
JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - Mar 20, 2017 at 03:15 PM
Hi Trowa,

Could you please revise the code to work based on this layout? I tried googling to see if I could make this work on my own but it just confused me more. https://www.dropbox.com/s/1rnoxpeicilcsv1/MAstersheert%20codes.pdf?dl=0

Thank you for all your help.
Reply
Leave a comment
Helpful
+0
plus moins
Hi JPSpark,

VBA can be quit confusing indeed.

Here is the revised code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub
Sheets("TO Sheet").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
Cells(Target.Row, "B")
Sheets("TO Sheet").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = _
Cells(Target.Row, "D")
Sheets("TO Sheet").Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = _
Cells(Target.Row, "C")
End Sub


Best regards,
Trowa
JPSpark 8Posts Tuesday December 27, 2016Registration date May 2, 2017 Last seen - May 2, 2017 at 08:52 AM
Thank you so much. This worked perfectly.
Reply
TrowaD 2282Posts Sunday September 12, 2010Registration date ModeratorStatus October 19, 2017 Last seen - May 2, 2017 at 11:00 AM
Great!
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!