Copy dynamic data, fill in

[Closed]
Report
Posts
2
Registration date
Wednesday September 26, 2012
Status
Member
Last seen
September 27, 2012
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

Is there anyone who can help me with this? I cannot figure it out and need this spreadsheet working asap for my boss and salesreps.
I've tried the IF function, Advanced Filter option, LOOKUP, but can't get anything to work and don't really know macros but am trying to learn.

Within my workbook there are 14 sheets but only 4 sheets that have Part #s that when a quantity >0 is entered in column E, the corresponding data from that row (ie: B4:G4) needs to copy over to the master material sheet 'MATL LIST' (ie starting at: A5:F5). And then as more parts/quantities are entered throughout each 4 sheets, the data from that row fills in the 'MATL LIST' sheet right after the last one entered.

1) '2 Voice-Evac'
2) '3 FARADAY'
3) '4 SINORIX'
4) '5 Sig Bat'
5) 'MATL LIST' (columns G:I must not change)

This seems like it should be an easy deal, but am drawing a blank for some reason.
Any help is very much appreciated!!

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
quote

1) '2 Voice-Evac'
2) '3 FARADAY'
3) '4 SINORIX'
4) '5 Sig Bat'
5) 'MATL LIST' (columns G:I must not change)
unquote
not clear

take a new work book with one sheet called MAIL LIST and the names of other sheets not relevant

goto vb editor (alt F11.hit control+R
you see all the opened workbooks
go to this relevant file and under this righclick the expression "thisworkbook"
in the window that opens copy this EVENT CODE

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub

Application.EnableEvents = False
If ActiveSheet.Name = "MAT LIST" Then GoTo exiting
If Target <> 0 Then
Range(Cells(Target.Row, "B"), Cells(Target.Row, "G")).Copy
With Worksheets("MAT LIST")
.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Offset(0, -3).PasteSpecial
If .Cells(Rows.Count, "A").End(xlUp).Row = 2 Then
.Range("A1:A3").EntireRow.Insert
End If
End With
End If
exiting:
Application.EnableEvents = True
Application.CutCopyMode = False

End Sub



now goto any sheet other than the sheet Mail List and enter some data in column E.
then got0 some other sheet (other than mail list) and enter a data in column E
now go to Mail list sheet see what happens

now if it is ok you can use this in a DUPLICAGTE OF YOUR FILE. KEEP THE ORIGINAL FILE SOMEWHERE SO THAT ORIGINAL IS NOT MESSED UP