transferring data to new multiple spreadsheets

  • Thread starter Thread starter pmiehle
  • Start date Start date
P

pmiehle

I utilize a large spreadsheet for my businesses orders and we use multiple (9) factories to build our product. My problem is that I send out weekly production schedules and it is time consuming to enter the data in to 10 different spreadsheets. Filtering, copying and pasting is a quicker option butthen i tend to lose my return data from the factories on the previous weeks.

What i am looking for is to transfer lines from my main sheet in to my production schedule but need it to filter by my factory (easy part) and then (hopefully) have it bring the information over sequentially on top of each other I.E. Row 1 may be factory XY, Row 2-10 is factory AB, Row 11 is factory XY again on main sheet. but when transferred over to production schedule for Factory XY, it simply posts to Row 1 and Row 2.

I am good with basic conditions and formulas, fair with VBA, but this I think might just be a pipe dream. any suggestions out there?
 
Hi,

Am Fri, 13 Jun 2014 14:22:30 -0700 (PDT) schrieb (e-mail address removed):
I utilize a large spreadsheet for my businesses orders and we use multiple (9) factories to build our product. My problem is that I send out weekly production schedules and it is time consuming to enter the data in to 10 different spreadsheets. Filtering, copying and pasting is a quicker option but then i tend to lose my return data from the factories on the previous weeks.

What i am looking for is to transfer lines from my main sheet in to my production schedule but need it to filter by my factory (easy part) and then (hopefully) have it bring the information over sequentially on top of each other I.E. Row 1 may be factory XY, Row 2-10 is factory AB, Row 11 is factory XY again on main sheet. but when transferred over to production schedule for Factory XY, it simply posts to Row 1 and Row 2.
Click to expand...

modify following code to fit it to your workbook:

Sub Test()
Dim myStr As String
Dim myArr As Variant
Dim i As Long, LRow As Long

'Modify factory names
myStr = "factory AB,factory XY,factory CD"
myArr = Split(myStr, ",")

Application.ScreenUpdating = False
'Modify sheet name
With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = LBound(myArr) To UBound(myArr)
'Modify used range in sheet1 and autofilter field
.Range("A1:G" & LRow).AutoFilter field:=1, Criteria1:=myArr(i)
.Range("A2:G" & LRow).Copy Sheets(myArr(i)) _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub

Regards
Claus B.
 
Back
Top