How to populate information on multiple sheets at onceSolved/Closed

Question

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.

TrowaD · Answer

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.

TrowaD · Answer

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

TrowaD · Answer

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

TrowaD · Answer

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

TrowaD · Answer

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

How to populate information on multiple sheets at once

5 responses

Similar discussions

Subscribe To Our Newsletter!