i want to update specific columns from one workbook

October 16, 2017 at 19:02:07
Specs: Windows 7
i have 3 workbooks
one is workorder where the columns are wono,odate,duedate,itemno,desc,qty

one is qtyinhand where the columns are
itemno,desc,qty

third one is prodschedule
in the third one everyday the workorder data must get updated automatically at the last row of existing data

after that we need to check the available qty in hand from second workbook
this is the process
kindly help me in how to do this
most urgent as i am assigned this task in my company


See More: i want to update specific columns from one workbook

Reply ↓  Report •

#1
October 17, 2017 at 05:49:40
Do you actually have three workbooks or do you have three worksheets within a single workbook?

This can be achieved with VBA but the code will be very different if there are 3 workbooks or just one. Please clarify this.

Also please bullet point the process in steps so this can be coded.

in the third one everyday the workorder data must get updated automatically at the last row of existing data

How is the workorder updated? is it a button press or is it a time event or do you just wish to run a process a the end of the day that does everything at once?


after that we need to check the available qty in hand from second workbook
this is the process

So you want to check the qty, but then what? what do you do with this quantity value? does it get updated, copied, deleted?

You really need to be prescriptive with your requirements/process otherwise I will be guessing...


Reply ↓  Report •

#2
October 18, 2017 at 18:54:11
Hi
thank u for the response.
1. The data is available in three workbooks not worksheets.
2. workorder data is received daily from the sales dept.
3. we need to maintain a production schedule sheet where in the last row,the workorder data has to be updated that too specific columns like wono,wodate,itemno,itemname,itemqty,duedate
4. after the work order data is updated daily by means of a command button or a procedure,we have to open the quantity on hand workbook and check the available qty for the work order item,display it in the qoh column of production schedule.
After this a calculation is done ie) orderqty minus qoh,if qoh is more then update the order status column as "can process today" if not display " send for production"

this is what is expected can u please help


Reply ↓  Report •

#3
October 24, 2017 at 02:33:05
luvyogesh100,

to make it easier for me to understand the layout of your workbook, you will need to detail EXACTLY how your workbooks are laid out.

Use the pre tags to format your worksheet data.

https://www.computing.net/howtos/sh...


For example

Workbook "Work Order"

Sheet "Work Order Data

                     A             B              C               D                         
1                   xyx          123              890           445 
B                   e             f               g              h
C                   321          555              423           www
D                   888          554              454           225

If you can do this for all three workbooks I can then understand what you want me to do, with your description above I am a little lost.

message edited by AlwaysWillingToLearn


Reply ↓  Report •

Related Solutions

#4
October 24, 2017 at 09:20:53
workorder
columns
A B C D E F G
1 WONO ORDERDATE DUEDATE CUSTNAME ITEMNO ITEMDESC ITEMQTY
2 w140 10/10.2017 20/10/2017 abc ltd 15563 xyz sheets 200
3
4
5

itemmaster
columns
A B C D E F G
1 ITEMNO ITEMDESC ITEMQTY
2 15563 xyz sheets 400
3 15564 xyy sheets 300
4
5

DATA from workorder is brought to the prodsched,QTYAVLBL is the qty available for the itemno in ITEMMASTER

prodsched
columns
A B C D E F G
1 WONO ORDERDATE DUEDATE ITEMNO ITEMDESC ITEMQTY QTYAVLBL
2 w140 10/10.2017 20/10/2017 15563 xyz sheets 200 600
3
4
5


Reply ↓  Report •

#5
October 24, 2017 at 09:22:10
i am ready to share the excel sheets also if required,kindly help me

Reply ↓  Report •

#6
October 24, 2017 at 23:53:17
You obviously didn't click on the link I provided!! please read the use of pre tags by clicking on the link, then try again otherwise I cannot help you.

Alternatively PM me and i'll tell you which email address to send your workbooks too

message edited by AlwaysWillingToLearn


Reply ↓  Report •

#7
October 25, 2017 at 06:59:21
i am unable to utilise the tag,kindly share your mail id,what is PM

Reply ↓  Report •

#8
October 25, 2017 at 07:19:11
Click on my name in blue just above this text, then when the new page opens, click on my name again, this time in white and send me a PM (Private Message)

Just wondering, why are you unable to use the Pre Tags?


Reply ↓  Report •

#9
October 25, 2017 at 19:15:13
hi
i have tried my best to pretag the data,if i get a solution today,i will not cut a sorry face in my office,kindly help

workorder
columns in worksheet

    A    B          C        D       E       F       G
1 WONO ORDERDATE DUEDATE CUSTNAME ITEMNO ITEMDESC ITEMQTY
2 w140 10/10/2017 20/10/2017 abc ltd 15563 xyz sheets 200
3 w140 10/10/2017 21/10/2017 xxf ltd 15563  xyz sheets 200


itemmaster
columns in worksheet

   A      B      C   
1 ITEMNO ITEMDESC ITEMQTY
2 15563 xyz sheets 400
3 15564 xyy sheets 300
4

5

DATA from workorder is brought to the prodsched automatically ,QTYAVLBL is the qty available for the itemno in ITEMMASTER

prodsched
columns

   A        B       C       D       E        F        G
1 WONO ORDERDATE DUEDATE ITEMNO ITEMDESC ITEMQTY QTYAVLBL
2 w140 10/10/2017 20/10/2017 15563 xyz sheets 200 600
3
4

5

Reply ↓  Report •

#10
October 25, 2017 at 19:16:37
the workorder must be automatically updated using a command button or can be a event

Reply ↓  Report •

#11
October 26, 2017 at 02:38:37
Ok I have a question.

QTYAVLBL is the qty available for the itemno in ITEMMASTER

in your

prosched
worksheet you have
QTYAVLBL
as 600 but on
ITEMMASTER
item 15563 only has 400, so whats going on here?

is this the quantity available plus the 200 that from workorder or was this just a mistake?

if it is indeed the quantity available before the workorder, then does the code need to adjust the value in the item master once the workorder is submitted?


Reply ↓  Report •

#12
October 26, 2017 at 03:01:31
Ok here is a start, the code will copy

WONO, ORDERDATE, DUEDATE, CUSTNAME, ITEMNO, ITEMDESC, ITEMQTY

from Workorder to ProSched

Then it will lookup each ItemNo in ProSched and find the QTYAVLBL from ItemMaster and put that in QTYAVLBL in ProSched

Please try and let us know how this worked

Sub CopyToSheet()
    
    Dim Bcell As Range
    Dim iCell As Range
    Dim NextRow As Long
    
    NextRow = Sheets("ProdSched").Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Sheets("Workorder").Range("A2", Range("F" & Rows.Count).End(xlUp)).Copy _
        Destination:=Sheets("Prodsched").Range("A" & NextRow)

        
    For Each Bcell In Sheets("ProdSched").Range("E2", Sheets("ProdSched").Range("E" & Rows.Count).End(xlUp))
        
        For Each iCell In Sheets("itemmaster").Range("A2", Sheets("itemmaster").Range("A" & Rows.Count).End(xlUp))
            
            If Bcell = iCell Then
                
                Bcell.Offset(0, 2).NumberFormat = "0"
                Bcell.Offset(0, 2) = iCell.Offset(0, 2)
            
            End If
            
            Next iCell
        
    Next Bcell

End Sub


message edited by AlwaysWillingToLearn


Reply ↓  Report •

#13
October 26, 2017 at 08:13:50
Really appreciate your effort to help me
I tried copying this code to the prodsched workbook,tried running the code,does not do anything.

must i keep workorder,itemmaster and prodsched data in three sheets of a workbook or seperately???


Reply ↓  Report •

#14
October 26, 2017 at 08:27:22
I have sent you the real excel data,kindly can you add the code in that

Reply ↓  Report •

#15
October 27, 2017 at 00:05:00
Ok, in your previous post you were referring to the sheets as if they were all in the same workbook, I have seen your workbooks and as they are separate this will require a bit more work, I will have a look at this when I can but cannot promise I can have it done today! maybe sometime next week,

Reply ↓  Report •

#16
October 27, 2017 at 02:19:23
Ok, you sent me three files

0140_W.xlsx
itemmaster.xls
prodsched.xlsx

Firstly, I have changed itemmaster from being a xls file to xlsx
The code has been saved to workbook '0140_W' and therefore its extension has been changed to '.xlsm'

NOTES
1) All files MUST be in the same location
For example
"C:\Yogesh\0140_w.xlsm"
"C:\Yogesh\itemmaster.xlsx"
"C:\Yogesh\prodsched.xlsx"

2) You only need to open '0140_w.xlsm and press the command button, this will then open the other files when it needs to...

As you have multiple sheets in ItemMaster I have assumed you want to use "CAMFG-MASTER" for the look up of quantities.

Here is the code.

Please use the workbooks I have sent you as I have updated the extensions and can confirm all works.....

Dim bCell As Range
Dim iCell As Range
Dim RootPath As String
Dim FileItemMaster As String
Dim FileProdsched As String
Dim iExcel, eExcel As Object
Dim iLastRow, LastRow As Long

Private Sub CmdInitialise_Click()
    iLastRow = Sheets("METAL").Range("B" & Rows.Count).End(xlUp).Row
 
    RootPath = ThisWorkbook.Path
    
    FileItemMaster = RootPath & "\itemmaster.xlsx"
    FileProdsched = RootPath & "\prodsched.xlsx"
    
    CopyToProdsched
End Sub

Private Sub CopyToProdsched()
        
    Set iExcel = CreateObject("Excel.Application")
    Let iExcel.Visible = True
    
    iExcel.Workbooks.Open FileProdsched
    LastRow = iExcel.Workbooks("Prodsched").Worksheets("prodsched").Range("A" & Rows.Count).End(xlUp).Row + 1
    Debug.Print LastRow
    
    Sheets("METAL").Range("B2", "B" & iLastRow).Copy
    iExcel.Workbooks("Prodsched").Worksheets("Prodsched").Range("A" & LastRow).PasteSpecial xlValues
    
    Sheets("METAL").Range("E2", "G" & iLastRow).Copy
    iExcel.Workbooks("Prodsched").Worksheets("Prodsched").Range("B" & LastRow).PasteSpecial xlValues
    
    Sheets("METAL").Range("I2", "K" & iLastRow).Copy
    iExcel.Workbooks("Prodsched").Worksheets("Prodsched").Range("E" & LastRow).PasteSpecial xlValues
    
    With iExcel.Workbooks("Prodsched").Worksheets("Prodsched").Columns(5)
        .NumberFormat = "0"
        .Value = .Value
    End With
    
    iExcel.Workbooks("Prodsched").Worksheets("Prodsched").Columns.AutoFit
    
    GetQuantities
        
End Sub

Private Sub GetQuantities()
    
    Set eExcel = CreateObject("Excel.Application")
    Let eExcel.Visible = True
    eExcel.Workbooks.Open FileItemMaster
    
    
    For Each bCell In iExcel.Workbooks("Prodsched").Worksheets("prodsched").Range("E2", iExcel.Workbooks("Prodsched").Worksheets("prodsched").Range("E" & Rows.Count).End(xlUp))
        For Each iCell In eExcel.Workbooks("Itemmaster").Worksheets("CAMFG- Metal").Range("A2", eExcel.Workbooks("Itemmaster").Worksheets("CAMFG- Metal").Range("A" & Rows.Count).End(xlUp))
        
            If bCell = iCell Then
                
                bCell.Offset(0, 3).NumberFormat = "0"
                bCell.Offset(0, 3) = iCell.Offset(0, 3)
            
            End If
            
        Next iCell
        
    Next bCell
    
iExcel.DisplayAlerts = False
iExcel.Workbooks("ProdSched").Save
iExcel.Application.Quit
    
eExcel.DisplayAlerts = False
eExcel.Application.Quit
    
MsgBox "All processes complete, Workbook 'ProdSched' has been updated"
    
End Sub

message edited by AlwaysWillingToLearn


Reply ↓  Report •

#17
October 27, 2017 at 06:27:41
Thanks a ton for spending so much time and effort to solve my problem in excel,i downloaded the files,copied all the three to a single folder,tried running the command button,it opens the prodsched and stops,when that is closed,it shows subscript out of range error in the following code
.
Private Sub CopyToProdsched()

Set iExcel = CreateObject("Excel.Application")
Let iExcel.Visible = True

iExcel.Workbooks.Open FileProdsched
error line:
LastRow = iExcel.Workbooks("Prodsched").Worksheets("prodsched").Range("A" & Rows.Count).End(xlUp).Row + 1
Debug.Print LastRow

what shall I do now,I think I have troubled u the maximum,i am unable to debug it
please help

Yogesh


Reply ↓  Report •

#18
October 27, 2017 at 06:45:13
Not sure what to suggest it is working for me absolutely fine.. Maybe close all other documents and files you have open and try again?

What version of office are you running?

message edited by AlwaysWillingToLearn


Reply ↓  Report •

#19
October 28, 2017 at 20:11:14
i am using office2013 professional,i tried executing the code from different ways,i get subscript out of range error,even i removed all the previous excel files and kept only these three files in a seperate folder in the desktop and tried also the workorder file will be received daily or weekly from sales dept.so we cannot place the command button everytime in the workorder workbook.If you are able to set it in prodsched workbook,and make me able to run the process,I shall submit it to my head,desperately telling lies to my head for getting this work done.

Reply ↓  Report •

#20
October 30, 2017 at 01:05:09
To be honest, this is what I was asking from you previously, you needed to tell me EXACTLY what the process is so that I do not have to redo the work. Now asking me to put the code into ProdSchd means re writing the code which I do not have time to do right now. I would suggest looking at the code and trying to figure it out for yourself, see what you can do post your code and problems and come back for assistance.

Reply ↓  Report •

#21
October 30, 2017 at 04:56:27
I clearly explained in the before posts that in prodsched the result is to shown anyway even if it is in workorder no problem but please help me in solving the " out of subscript range" error when i run the code

Reply ↓  Report •

#22
October 30, 2017 at 05:16:50
I am really happy that someone from computing.net is helping me to solve my problems in excel but it takes a lot of time to understand each other and share the requirement,hope I need to be more explanatory with my data and requirement,still struggling for a solution.

Reply ↓  Report •

#23
October 30, 2017 at 06:22:33
Ok please confirm you have not changed the names of any of the three files and have not changed any of the sheets within the workbooks?

The code executes perfectly on my machine so I am very confused as to why it wont on yours

error line:
 LastRow = iExcel.Workbooks("Prodsched").Worksheets("prodsched").Range("A" & Rows.Count).End

This could be because it cannot find the file called "Prodsched" or cannot file the worksheet called "Prodsched" within Workbook "Prodsched"

Please check


Reply ↓  Report •

#24
October 30, 2017 at 20:58:38
everything is fine even i have removed the other files from the system,i have created a folder in the desktop,copied the three files which u have sent me in email, opened the workorder file and tried running the code,again i get the same error,subscript out of range,it opens prodsched with blank rows.

Reply ↓  Report •

#25
October 30, 2017 at 20:59:31
I tried running the code in another system also,i get the same error,the same line

Reply ↓  Report •

#26
November 15, 2017 at 06:11:20
Hi,

How is this going did the code finally work for you?


Reply ↓  Report •

Ask Question