extract certain value and copy to different workbook

[KARTINA AZMAN]

Hi.. i am cracking my head here trying to figure out how to do this. What I would like to do is to extract the value in column G of a workbook (different every month) and copy the value to a master workbook, into corresponding columns. Here is what I've got so far

Sub extract()
Dim master As Worksheet
Dim procCode, MIScode As String
Dim mnth As Integer
Dim i, j, lastrow As Long

Set master = ThisWorkbook.Worksheets(3)

With ActiveSheet
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

'MsgBox lastrow

mnth = InputBox("Enter number of corresponding month", "MONTH INDEX")
For j = 8 To 77
    procCode = master.Cells(j, 2).Value         'j is row counter for master
        For i = 5 To lastrow
            MIScode = ActiveSheet.Cells(i, 2).Value     'i is row counter for master
                If ActiveSheet.Cells(i, 1).Value = "OUTPATIENT" Then
                    If procCode = MIScode Then
                    ActiveSheet.Cells(i, 7).Copy master.Cells((mnth + 6), j) 'value to copy is in activesheet,col G to copy to master.worksheets(3)
                    End If
                ElseIf ActiveSheet.Cells(i, 1).Value = "EMYpatient" Then
                    If procCode = MIScode Then
                    ActiveSheet.Cells(i, 7).Copy master.Cells((mnth + 19), j) 'value to copy is in activesheet,col G to copy to master.worksheets(3)
                    End If
                ElseIf ActiveSheet.Cells(i, 7).Value = "Inpatient" Then
                    If procCode = MIScode Then
                    ActiveSheet.Cells(i, 7).Copy master.Cells((mnth + 32), j) 'value to copy is in activesheet,col G to copy to master.worksheets(3)
                    End If
                End If
        Next i
Next j

End Sub

After debugging so much, no more error showing but i do not get the result that i want.

Kindly help. I'm losing my mind.

 

[Marc L]

Hi !

In which workbook code must be located ?
Which worksheet is the source data to copy to master workbook ?
​

 

[vletm]

KARTINA AZMAN
Something like this ...
Press [Do Copy]-button ...

 

[KARTINA AZMAN]

Hi !

In which workbook code must be located ?
Which worksheet is the source data to copy to master workbook ?
​

I am putting the code in MASTER.xlsm. The other workbook is going to be different every month; the IT department will send it to me monthly.

 

[KARTINA AZMAN]

KARTINA AZMAN
Something like this ...
Press [Do Copy]-button ...

@vletm , Oh my.. wonderful. I am trying to digest your code.. i am confused with the reference used though..
I am lost when i reach this part

ys = WorksheetFunction.Match(chk_b, Workbooks(WB_1).Sheets(s_tab).Range("B:B"), 0)
            If Err.Number = 0 Then
                xs = WorksheetFunction.Match(chk_a, Sheets(s_tab).Rows("5:5"), 0)
                xxs = WorksheetFunction.Match(m, Sheets(s_tab).Range(Sheets(s_tab).Cells(6, xs), Sheets(s_tab).Cells(6, xs + 11)), 0) + xs - 1
                Sheets(s_tab).Cells(ys, xxs) = chk_g
                Sheets(s_tab).Cells(ys, xs + 12) = WorksheetFunction.Sum(Sheets(s_tab).Range(Sheets(s_tab).Cells(ys, xs), Sheets(s_tab).Cells(ys, xs + 11)))
                Sheets(s_tab).Cells(ys, xs + 12).Font.Bold = True
                Workbooks(WB_2).Sheets(chk_tab).Cells(y, 2).Font.Bold = True
                ccc = ccc + 1
            End If
            y = y + 1
        Loop Until Workbooks(WB_2).Sheets(chk_tab).Cells(y, 2) = Empty

Also, if we re putting the code under Private Sub, we do not have to declare anything?

I am a novice of vba with big hope to master it. Thanks for your guidance.

 

[vletm]

KARTINA AZMAN - 'My solution' is 'my way' to do this.
Where did You lost?
1) Do it work as You dream?
2) If so ... then if You need to know how do it work then
You would run it step-by-step and ... then You'll see what/where/why/how...
Private Sub and declare have no connection!
(many has, I call 'top row', and after that code have to have that part too!)
If You want to declare variables then You can do it - I don't need.
Novice to Master: ... it needs work work work ... and many times work.

 

[KARTINA AZMAN]

KARTINA AZMAN - 'My solution' is 'my way' to do this.
Where did You lost?
1) Do it work as You dream?
2) If so ... then if You need to know how do it work then
You would run it step-by-step and ... then You'll see what/where/why/how...
Private Sub and declare have no connection!
(many has, I call 'top row', and after that code have to have that part too!)
If You want to declare variables then You can do it - I don't need.
Novice to Master: ... it needs work work work ... and many times work.

Hi again.. I invest some time to understand the code already.. From what I understand, lookup value is from the monthly (WB_2) , then look for a match in the master workbook, and put the value in corresponding month. as you can see, some of the value is not transmitted because in master workbook the code has "OS" at the end of the code whilst in the WB_2 (used as lookup value) there is no "OS" at the end of the code. is using Select Case the best way to address this?

 

[vletm]

KARTINA AZMAN
So far if 'Order Item' is JUST SAME then it will notice!
Some 'Order Items' has -OS, -EDSE, -ESE ... and so on.
Please, give an example which kind of combination should move too.
If have to notice ONLY 1st 6 characters then that would be okay too
or
If You want to select 6 characters and with or without OS
then I could try to do it - okay?
Select case ... not work in this case!

 

[KARTINA AZMAN]

1. Copying based on first six string won't work since there are items like 75A001OS,75A001(EVQ). I think that will be a problem,no?
The only needed figures that are those with OS .Like;

WB_1 WB_2
75A001OS 75A001
75A003OS 75A003
75A033OS 75A033
75A008(3D)OS 75A008(3D)

2. At the beginning of your code there is ccc=0 and at the end is ccc=ccc+1. I know that it serve as a sort of counter but what is it that it counts?

3. xxs = WorksheetFunction.Match(m, Sheets(s_tab).Range(Sheets(s_tab).Cells(6, xs), Sheets(s_tab).Cells(6, xs + 11)), 0) + xs - 1 <----- I manage to catch the match function part but have not figure out why there is +xs-1 at the end. Would you kindly explain to me?


Thanks

 

[vletm]

KARTINA AZMAN
1. Now it checks both (with and without OS) and if(then) then add that value
(add means, if there has been already value, then adds new value to present.)
2. ccc is counter for '(Bolded) Order Items Copied' >> the last message
3. +xs-1 ... the smallest Match-value can be 1 and this needs 0 too.
> Ideas ... Questions?

 

[KARTINA AZMAN]

KARTINA AZMAN
1. Now it checks both (with and without OS) and if(then) then add that value
(add means, if there has been already value, then adds new value to present.)
2. ccc is counter for '(Bolded) Order Items Copied' >> the last message
3. +xs-1 ... the smallest Match-value can be 1 and this needs 0 too.
> Ideas ... Questions?

hi.. sorry I did not manage to spend time last week to go through your code. I just tried it (I deleted all the values and press the command button again) but those with "OS" did not get copied. Did I miss something?

 

[vletm]

KARTINA AZMAN
Did You used same '02-NCL...' file?
... there are NONE -OS ending 'Order Item's ... hmm?
Would You name few rows, where should happen something?
From '02-NCL...' to 'MASTER'.

 

[KARTINA AZMAN]

KARTINA AZMAN
Did You used same '02-NCL...' file?
... there are NONE -OS ending 'Order Item's ... hmm?
Would You name few rows, where should happen something?
From '02-NCL...' to 'MASTER'.

yes sir.. I am still using the sam '02-NCL.." file.
"
Would You name few rows, where should happen something?
From '02-NCL...' to 'MASTER'.[/quote]" <--- do u mean those rows in the MASTER list that does not get populated with values from "'02-NCL.."?
-- if so, the rows involved are 8,14,17,20,23,24 and 35.
-- strangely enough, values for items at rows 36 to 40 (which happens to be -) is being copied through.

 

[vletm]

Row 8 from Master and ... possibles from '02-NCL..' one sheet

Only 'MASTER' has those'OS' endings!
What about if 'MASTER' has also without that 'OS'?
Do You want that if match from '02-NCL...' to 'MASTER' or how?
Try to write
what should copy with that '75A001' with or without case ...
from which file/sheet/row/column and which value (if previous value then what?)
to which file/sheet/row/column?
> I try to figure later - gotta go now

 

[vletm]

KARTINA AZMAN - case 75A001 ...
I tried to check again and again ...
Still I cannot figure what...
Below is '02-NCL...' files 75A001 data rows; three

1) Those will shown in Master-file with 75A001
(or do You want that those would show ALSO with 75A001OS ??? )
2) I add some bolding to Master-file; if Order Item has match then bolded
3) also I changed that one cell can have only one value = if double data then the last value will show (because, if run 'Do Copy' more than once in month, wrong values!)
4) also ZERO-values will show with gray font
>> Ideas ... Answers ... Questions?

 

[KARTINA AZMAN]

KARTINA AZMAN - case 75A001 ...
I tried to check again and again ...
Still I cannot figure what...
Below is '02-NCL...' files 75A001 data rows; three
View attachment 40920
1) Those will shown in Master-file with 75A001
(or do You want that those would show ALSO with 75A001OS ??? )
2) I add some bolding to Master-file; if Order Item has match then bolded
3) also I changed that one cell can have only one value = if double data then the last value will show (because, if run 'Do Copy' more than once in month, wrong values!)
4) also ZERO-values will show with gray font
>> Ideas ... Answers ... Questions?

I ended up deleting all the "OS" appendages and mark it down / write the full code with OS only as comment. this way, all the values got transferred smoothly.. thanks a lot!!!!