COPY PASTE DATA FROM MASTER WORKBOOK TO ALL WORKBOOKS IN THE FOLDER BASED ON CRITERIA

Hello everyone,

I was recently transfered to planning department in my company, and I found myself drowning in about 40 excel files to manipulate manually, for every small change I nedd to do for an item on a machine. That is impossible for me as it will quickly create confusion regarding what I already did in one file but didn't modify in the other/s.

What I have to work with:

1. I have a file named Pressing in real time, which I change manually as per need of the factory floor. Every machine is listed in column A of the sheet Master, and it has multiple rows, as one machine usually does two to five items in one day.

2. Then after I mannually update Pressing in real time I have to go and mannually update the specific machine workbook, which are in the same folder. For every machine there is a workbook, named with the machine mumber, so from 1 to 23. As moving more items from one machine to another may occur two or three times a day, it is very easy to mess things up, by opening, cutting, copying pasting....

What I would very badly need at the moment:

1. I will manually update the wrkbook Pressing in real time, specifically the sheet Live pressing

2. I would need a macro that I would associate to a command button on the Live pressing sheet, and when I click it it would have to copy and paste rows in the machine workbooks, based on column A criteria. SO the rows which in column A in MAster have number 17 listed, would have to be pasted into the workbook n. 17. The rows which have 19 in column A in MAster would have to be pasted in workbook named 19., and so on for every machine listed in column A

3. The master sheet, has also the column DAYS NEEDED. Now, I don't know if this is feasable. but I would also like that when the loop finds the number 2 listed in column H of the live pressing sheet , when copying the rows into the respective machine wkbks, I would like to paste them with one blank row underneath them ( because that item will take two days on the machine).

I attach the example file with fictionary data...I really hope someone can help me, as otherwise it can be very difficult not to mess up in planning the machines for the floor personnel... thank you very much in advance, any help will be highly appreciated.

vcoolio, yes I believe having machine sheets in the pressing wbk is a much better idea.

Thank you so much for the sample file and code. I will try it right away, and let you know how it works.

Thank you so very much for your help, I very much appreciate it, really.

Quote from vcoolio

Hello Clairexcel,

Here's a sample of what I mean using this code:-

Code

Option Explicit
Sub Test1()


Dim ar As Variant, i As Long, c As Range
Dim wsD As Worksheet, ws As Worksheet
Set ws = Sheets("Master")
ar = Array("17", "18", "19", "20")


Application.ScreenUpdating = False


For i = 0 To UBound(ar)
Set wsD = Sheets(CStr(ar(i)))
wsD.UsedRange.Offset(3).Clear
With ws.[A1].CurrentRegion
.AutoFilter 1, ar(i)
Union(.Columns("B:E"), .Columns("G:H")).Offset(1).Copy wsD.Range("B" & Rows.Count).End(3)(2)
.AutoFilter
End With
For Each c In wsD.Range("G4", wsD.Range("G" & wsD.Rows.Count).End(xlUp))
If c.Value = 2 Then
c.Offset(1).EntireRow.Insert
End If
Next c
wsD.Columns.AutoFit
Next i


Application.ScreenUpdating = True


End Sub

Display More

in the attached sample.

Click on the blue button to see how it works.

I hope that this helps.

Cheerio,

vcoolio

Clairexcel.xlsm

Display More

vcoolio, It is perfect , not only copies the rows pertaining the machine but also leveas one blank row if priority number is 2.

I really can't express how much you have helped me with this file, I really couldn't manage the amount of work with copy/paste in 23 files, without making errors... this file will save my work.

A huge thank you!