• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to import data from one category into another

P

PipBoy808

Member
I have two workbooks. The master workbook categorises deliveries by country. This includes headers that say 'Country XYZ' and then columns for dates, PO numbers, delivery size etc. I'd like to import this data into a second workbook. The second workbook categorises the deliveries by the day on which they took place. So, the headers in this workbook say something like 'Monday', with similar columns concerning PO number, delivery size also. Normally I'd do something like:

Code: 
TargetBook.Worksheet1.Range("A1:A10")=SourceBook.Worksheet1.Range("C1:C10")

but now it isn't so simple. I almost need a sort of COUNTIF function. So something like:

Code: 
Targetbook.Worksheet1.Range.("A1:A10) = [COUNTIF.SourceBook.Worksheet1.Range("B1:B999")=Monday]

... or something to that effect if that makes sense. Can anyone help me?
 
I'm afraid you lost me with the COUNTIF. Are you expecting the results from book1 to book2 to be a filtered result (aka, not all of the records from B1:B999 are getting copied over), or are they a summation of specific data? Perhaps a sample workbook with 2 sheet showing before/after?
 
An example would of course help! Attached is some dummy data. I'm in need of some code that will export the data from the source sheet to the target sheet based on the day the deliveries occurred, rather than the country in which they took place. I need to filter the source data somehow for each day of the week.
 

Attachments

  • ForChandoo.xlsm
    14.1 KB · Views: 5
Hi PipBoy,

I think I got a possible macro going for you. It uses Advanced Filter to setup the data, then transfers it over. Hopefully it gets you started.
 

Attachments

  • ForChandooWithMacro.xlsm
    28 KB · Views: 25
Thanks, Luke. I'm looking through the code now to try and get my head around it. Did you write it from the perspective that if there were a button to launch the macro, it would be in the source sheet? I just want to make sure that I'm looking at the code from the right point of view. As it happens, I'm going to place the button in the target sheet.
 
The code should be robust enough to handle whichever starting location you are at. That is the reason why I put the different workbook.activate and sheet.select commands in. Using all the "Set"s at the beginning let me control better where information is coming/going to. The one assumption I made was that the destination workbook is already opened. If that is not correct, you'll just need to add a line to open it first.
 
OK, I have a few questions (Sorry! :oops:) . I'm on the cusp of understanding this completely and it's going to be really beneficial when I do:

Code: 
myDay = Format(i, "dddd")

It's not that I don't understand this, but rather that I'm curious about the "dddd" format. Does this take the first 4 letters of each day, so 'Mond', 'Tues', etc?

Code: 
.Range("CritCell").Value = myDay

Critcell = P2 = "Saturday". Why did you choose to start with Saturday?

Code: 
        'Run the advanced filter
        dataRange.AdvancedFilter xlFilterCopy, .Range("P1:P2"), _
            .Range("FilterRange")

Could you explain the code for the filter? For instance, the first line and why the range P1 : P2 is important. I do understand some of it. 'FilterRange' (R1:V1) looks like a reference you've generated containing the main data headings for the code to filter through...right?

Code: 
        lastRow = .Cells(.Rows.Count, "V").End(xlUp).Row
        If lastRow = 1 Then
            'No records found
            Set fRange = Nothing
        Else
            Set fRange = .Range("R2", .Cells(lastRow, "V"))

I'm not sure why the first line of this is necessary, given that there only ever seems to be one row of data (R2:V2). This ties into my question about myDay = Saturday, but what is the significance of R2:V2, and why are the rows below it always blank?

I realise I'm not holding back in my curiosity here, I just really dig learning this stuff. If I can get this kind of filter to work then it'd be a big help. I feel like I should make a charitable donation in your name or something at this stage. Thanking you in advance! :)
 
No worries Pipboy, those were all excellent questions, and I can tell that you actually want to learn the "why" rather than just a "here's a problem, please fix it". That type of question is much more enjoyable to answer. :)

1. Why the format?
The Format method uses the same formatting options that you can use when formatting cells. 1 or 2 d's would give you a number, 3 d's gives you the 3 letter abbreviation for day, and anything more than that gives you the full name. So, the code says to take the number (1-7) and turn it into a day anme (Sunday, Monday, Tuesday...)
In the workbook, you could see this with the formula:
=TEXT(1,"dddd")

2. Why Saturday?
Actually, we start with Sunday, as you'll see with formula stated above. Saturday is the equivalent of 7, which is the last number we loop through. As for which day I start with, somewhat arbitrary. I just picked 1-7. Could also do 2-8 if you want to start with Monday.

3. Explain Advanced Filter
dataRange.AdvancedFilter xlFilterCopy, .Range("P1:p2"), _
.Range("FilterRange")

There are 3 arguments given for the AdvancedFilter method, and 1 range that it is being called against. The dataRange is the area that is being filtered, and is defined earlier in the code. The first argument states that we are copying to another location, not just filtering in place. The 2nd arguement is the Criteria Range, which tells XL what headers to filter on, and by what criteria. The last argument is where to paste the data to. FilterRange is a named Range in the workbook, defined as R1:V1. I prefer using Named ranges incase you need to move stuff around, the code doesn't have to be changed.
More info on Advanced Filter: http://www.contextures.com/xladvfilter01.html

4. Why the If check?
It's possible that our filter returns no results. In this case, the lastRow variable will be 1 (aka, just the header row). Since I don't want to paste the headers into the Target sheet, we need to then skip this day. Hence, the If check. With the sample data, Thursday and Friday fell into this situation. Other days like Monday and Tuesday have multiple records. Those records will start in row 2 (aka, R2) and then go down some variable amount of rows that I needed to be able to detect.

To aid in understanding the code, it might be helpful to step through the code using F8 rather than a straight run. Using F8 goes through the code one line at a time, allowing you to watch what's going on in the workbook (you can flip back and forth between the workbook and VBE).
 
Great answer. A couple of things I'd love clarification on. The plot thickens:

Code: 
i = 1 to 7

In the context of days of the week, does 1-7 always means Sunday-Saturday in VBA or is it specific to the way the data is arranged in the workbook?

Finally, what is the purpose of the array R2:V8 and why is there only ever one row of data therein?

Edit: BOOM! Stepped through the code and I see that R2:V8 provides a point of reference for Excel to see the necessary headings and the code for each day, from which it can copy it and paste it into the next sheet. This is the exclamation of a man who is beginning to understand AdvancedFilter!! :D
 
In the context of days of the week, does 1-7 always means Sunday-Saturday in VBA or is it specific to the way the data is arranged in the workbook?
Click to expand...

Yes and no. The more common way of thinking of days of the week is when in the workbook, we use the WEEKDAY function. Now, this function lets you choose what day of the week you want to be the starting day, but by default Sunday is called the first day of the week, and thus Sunday = 1, Monday = 2, etc.

However, that's not quite what's going on in the code. When we use the numbers 1-7 in the code, VB is literally taking the number 1, and converting it to a date. In XL, dates start with 1 = Jan 1, 1900. Now, it just so happens that Jan 1 of 1900 was a Sunday, so when I format that number/date to show me the day (the dddd bit), that 1 = Sunday, 2 = Monday.

That's why I said you could shift the number range to be 2-8, 3-9, etc. You would actually be shifting the range from Jan 2 - Jan 8, Jan 3 - Jan 9, which have different days.
 
That's hilarious. A very practical, if vague reason that will likely affect coding for decades to come. I understand it a lot more now. I'm going to try and apply an advanced filter to a similar workbook (albeit with more data) to the one I uploaded. Wish me luck!
 
OK, there's a slight difference between the example I gave and that to which I'm applying the code.

You wrote:

Code: 
'Define where our source data is
With SourceSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set dataRange = .Range("A2", .Cells(lastRow, "L"))
End With

I'd like to accomplish the same thing, but only within the range A15:L93. I've tried this:

Code: 
With Sourcesheet
mastertb = Worksheets("Worksheet1").Range("A15:L94")
Set DataRange = mastertb

Is that an eloquent alternative?
 
Not quite. In the first block, a variable is used to find last row in table, and then we use that to define our table.
If you already know that the length of the table is constant (or don't need it to be dynamic), then yes, we could define it directly.
However, there is a small error in your code. This:
mastertb = Worksheets("Worksheet1").Range("A15:L94")
will error out, as Range objects need to be "Set", as in the next line. I think it needs to be:
Code: 
Set DataRange = Worksheets("Worksheet1").Range("A15:L94")
 
Code: 
'Run the advanced filter
DataRange.AdvancedFilter xlFilterCopy, Worksheets(Weeeknum).Range("N69:N70"), Range("FilterRange")

As you can see, I've taken the above portion of your code and tweaked it to fit into my workbook as an Advanced Filter. The cell references are different, but refer to the same data, and the "FilterRange" definition refers to the little table you generated to temporarily store copied data. However, I keep getting a 'subscript out of range' error.

To be fair, you'll notice that I altered the layout of your code a little. However, if I paste it in exactly and just change the cell reference:

Code: 
        dataRange.AdvancedFilter xlFilterCopy, .Range("P1:P2"), _
            .Range("FilterRange")

I get a "Run-time error 1004: The extract range has a missing or invalid field name."

Any idea as to why either of these errors might occur if I'm trying to piece together code from what you wrote?
 
Hi, PipBoy808!

You're using:
.Range("P1: P2")
and:
.Range("FilterRange")
so regarding the initial "." I assume that this code is within a With...EndWith block. If so have you checked that "FilterRange" exists as a defined name with the proper scope?

Regards!
 
Adding on to what SirJB7 said, Advanced Filter is picky in that the sheet you are copying To needs to be the active sheet. Not sure if that's what's going on, but knowing where the With Statements are should help.
 
Adding on to what SirJB7 said, Advanced Filter is picky in that the sheet you are copying To needs to be the active sheet. Not sure if that's what's going on, but knowing where the With Statements are should help.​
Click to expand...

Ah, I think that might have something to do with it. I'll bear that in mind. When you say copying to, do you mean the destination sheet or the sheet containing "FilterRange".

I think I might have to make up a dummy workbook to show you what I'm working with. Watch this space. So close to the end!
 
Attached is a mock-up of what I'm dealing with. The layout is the same and the name definitions have been included.
The code is launched from a separate target workbook (WHTarget). It's designed to open this source workbook, copy the necessary data, and then close it. Here's what I have so far:

Code: 
Dim MasterSource As Workbook, WHTarget As Workbook
Dim WeekNum As String
Dim filepath As String
Dim truckfilename As String
Dim lastRow As Long
Dim mastertb As Range
 
Set WHTarget = ThisWorkbook
 
Application.StatusBar = "Opening master workbook."
'Open the Master Workbook
filepath = Worksheets("Reference").Range("G3").Value 'this is a filepath reference within the target workbook
truckfilename = Worksheets("Reference").Range("G4").Value 'this is a file name reference within the target workbook
Set MasterSource = Workbooks.Open(filepath & truckfilename)
 
'make sure that only data for the current week is extracted (In the attached workbook, the 'Source' sheet is taken from Worksheets(Weeknum) in the real thing. I just need to distinguish between different weeks)
WeekNum = "W " & Application.WorksheetFunction.WeekNum(Date)
 
'Define where source data is
With MasterSource
Set mastertb = Worksheets(WeekNum).Range("P70:U84")
Set DataRange = mastertb
End With
'I didn't use lastrow here because I know the data area I need to copy from.
'It's the extra table in the attached workbook
 
For i = 1 To 7
    MasterSource.Activate
    Worksheets(WeekNum).Activate
    With Worksheets(WeekNum)
    myDay = Format(i, "dddd")
    .Range("CritCell").Value = myDay
    .Select
'***********************************************
'I can step through the code up to here with no issue.
'***********************************************
'Run the advanced filter
        DataRange.AdvancedFilter xlFilterCopy, .Range("N69:N70"), _
            .Range("FilterRange")
'I will of course be adding to all of this. I just want to get the filter working before writing more.
    End With
    Next
End Sub

The last few lines are the problem area. Based on all of this, can anyone see why the filter doesn't seem to be working? It feels like a 'So close, yet so far.' situation.
 

Attachments

  • ChandooDummy.xlsx
    15 KB · Views: 3
OK I've realised one silly error that I made:

Code: 
With MasterSource[/COLOR][COLOR=#000000]Set [/COLOR]
[COLOR=#000000]mastertb = Worksheets(WeekNum).Range("P70:U84")[/COLOR]
[COLOR=#000000]Set DataRange = mastertb[/COLOR]
[COLOR=#000000]End With
Should read:

Code: 
With MasterSource[/COLOR][COLOR=#000000]Set [/COLOR]
[COLOR=#000000]mastertb = Worksheets(WeekNum).Range("A15:L94")[/COLOR]
[COLOR=#000000]Set DataRange = mastertb[/COLOR]
[COLOR=#000000]End With
In spite of the DataRange from which the data is to be filtered being corrected, I still get the same error:

"Run-time error 1004: The extract range has a missing or invalid field name."
 
Hi PipBoy

I found a few little things, and then the reason why your code doesn't work on the filter. First, in this block:
Code: 
With MasterSource
'CORRECTION - When using With Statements, don't forget the leading "."
'It works currently because MasterSource is already Active, but your code here
'doesn't do what you think it was doing
Set masterTB = Worksheets(WeekNum).Range("A15:L94")
Set DataRange = masterTB
End With
Note that the Set MasterTB line does not have a . before the Worksheets. Not a code breaker, but something to watch out for especially since we will be switching between workbooks.

Code: 
    With Worksheets(WeekNum)
    myDay = Format(i, "dddd")
    'CORRECTION - You don't appear to have a cell named CritCell in the sample workbook
    .Range("CritCell").Value = myDay
    'CORRECTION - This is selecting the WeekNum sheet...which is already active
    .Select
See comments above. The first may be something you've already fixed in actual document, the latter is just an unnecessary step.

Now, the big reveal as to why things aren't working... :)
Code: 
DataRange.AdvancedFilter xlFilterCopy, .Range("N69:N70"), _
            .Range("FilterRange")
I'm assuming that FilterRange is already defined in your workbook as being ~ P69:U69. However, the reason the code is crashing is that the column headers don't match. You need to use the same labels as the headers in the range you are pulling from. Also, I'd avoid Merged Cells. Thats going to mess you up here, and as a rule, they wreak havoc when trying to write code.
 
Brilliant stuff. It's always the simplest details ...

When you say I need to lead 'With' statements with a ".", do you mean I should write:

Code: 
With .Mastersource

or did you mean that the "." should be placed elsewhere?

I must be learning, because I've identified a place where my code could be a little more robust. I think for the sake of the longevity of the code, it would be best if the "FilterRange" table was more dynamic after all. So, in the attached workbook I've moved it to its own worksheet and incorporated the 'lastrow' variable you wrote to identify the extent of it. Unfortunately, I don't know how to change the AdvancedFilter in order to reflect this. It looks like this as it stands:

Code: 
        DataRange.AdvancedFilter xlFilterCopy, Worksheets("Reference").Range("B6:B7"), _
            .Range("FilterRange")
        lastRow = .Cells(.Rows.Count, "O").End(xlUp).Row

Where ("B6:B7") refers to cells in the "Reference" worksheet. Is it possible to incorporate this into the filter? The reason it's all in its own sheet is because the original sheet I'm using is too cramped, so the 'lastrow' variable wouldn't work.

To be fair, the only reason I've complicated things is because I saw you (Luke M) demonstrate a better way of doing things, which gave me the idea :p

This thread is becoming quite epic.

PS I hope the headings, name definitions, and my use of 'lastrow' are correct.
 

Attachments

  • ChandooReferenceDummy.xlsx
    15.9 KB · Views: 5
The . needs to be placed elsewhere. It goes in front of each item that you want to trace back to your With.
Example:
Rather than writing:
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
ThisWorkbook.Worksheets("Sheet1").Range("A2").Select
ThisWorkbook.Worksheets("Sheet1").Range("A3").Select

you can write
With ThisWorkbook.Worksheets("Sheet1")
.Range("A1").Select
.Range("A2").Select
.Range("A3").Select
End With

Note how each line has a period, indicating that the line is really ThisWorkbook.Worksheets("Sheet1").Range("A1")

On your Filter Range, I should clarify what I said before about headers. While the header names need to match, you don't have to have all of the headers. You have the option to pick and choose which ones you want, like in the original file I posted.

I've modified your code so that it pastes to the Filter data to your Reference worksheet. Note that everything just keeps getting pasted from the filter to this same spot, and you will need additional code to mode that data to your final destination (like in the workbook I posted)
Code: 
Sub FilterCode()
Dim MasterSource As Workbook, WHTarget As Workbook
Dim MasterSheet As Worksheet, HelperSheet As Worksheet
Dim WeekNum As String
Dim filepath As String
Dim truckfilename As String
Dim lastRow As Long
Dim mastertb As Range
 
 
Set WHTarget = ThisWorkbook
 
Application.StatusBar = "Opening master workbook."
Application.ScreenUpdating = False
 
'Open the Master Workbook
filepath = Worksheets("Reference").Range("G3").Value 'this is a filepath reference within the target workbook
truckfilename = Worksheets("Reference").Range("G4").Value 'this is a file name reference within the target workbook
'Set MasterSource = Workbooks.Open(filepath & truckfilename)
 Set MasterSource = ThisWorkbook
'make sure that only data for the current week is extracted (In the attached workbook, the 'Source' sheet is taken from Worksheets(Weeknum) in the real thing. I just need to distinguish between different weeks)
WeekNum = "W " & Application.WorksheetFunction.WeekNum(Date)
 
'Use this line if finding variable worksheet
'Set MasterSheet = MasterSource.Worksheets(WeekNum)
'Using this line for test purposes
Set MasterSheet = MasterSource.Worksheets("Source")
Set HelperSheet = MasterSource.Worksheets("Reference")
 
With MasterSheet
    'Need to find the last row before we determine where to get data from
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set DataRange = .Range("A15:L" & lastRow)
End With
 
 
For i = 1 To 7
    'Select the source workbook, just to be sure
    MasterSource.Activate
            
    With HelperSheet
        'Since the AdvancedFilter (AF) is pasting to Reference sheet
        'need to make sure that it is the active sheet
        .Select
        myDay = Format(i, "dddd")
        .Range("CritCell").Value = myDay
                
    'Run the advanced filter
       DataRange.AdvancedFilter xlFilterCopy, .Range("Criteria"), _
            .Range("FilterRange")
        'At this point we'll then copy the data from this table
        'to somewhere else
   End With
Next
 
'Reset the status bar
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
 
Thanks, I definitely understand the logic behind the "." and the 'With' statement now. I'[m aware that I need to write more code to paste the filtered data to my destination. I'm on the case! Hopefully I'm one step closer to putting this to bed.
 
I genuinely understand every line in the code now (so thanks a lot for that, I've come along in leaps and bounds! :)) , but the filter is still not working. I have it in word for word and can step through the code just fine up until the filter, when I get:

"Run-time error 1004: Method 'Range' of object '_Worksheet' failed"

That seems like quite a specific error.

On the plus side, you used the named cells to great effect. I definnitely have some ideas about how I can use named cells/ranges/arrays to great effect in future. It's just the darn filter.
 
You must log in or register to reply here.
Back
Top