To Macro or Not to Macro?

[enigmahack]

I don't know if excel can do this without VB, but I basically have 2 forms.

Form A has a column that is going to basically retreive data.
Form B has columns of data, and IF the data exists in that row, it has a 1 in row J in that form.

Basically what I want to do is "If the #1 exists at the end of the row, copy that row into the next free space under Form A"
*So that it only copies over values that exists, so I don't get a whole bunch of #N/A*

Any ideas on how you might be able to do this?

Thanks in advance!

 

[HalfAce]

To macro - Always!
I'm sure Aladin and many others who are actually good at formulas would disagree.

What are the names of the sheets involved? (The source sheet and the destination sheet?)

What column in the source sheet is a reliable column to determine the last row of data?

 

[enigmahack]

The sheets names are basically "Sheet1" which is the main data "finder" sheet, Sheet2, Sheet3 and Sheet4 are the sheets that the information is coming from.
The column that will contain the 1's to state if it's necessary to copy the data is going to be column L in sheets 2, 3 and 4.
The information that I'm getting (if the value 1 exists in column L) are names from column I on sheets 2, 3, and 4.
The placement will differ per sheet though: When sheet2's data is placed on sheet1, it'll place the data in Column D, Sheet 3 would be Column E and Sheet 4 would be Column F.

Thanks for your help!!

 

[enigmahack]

Okay, here's what I've got that kind of works. There's a value on the main page that counts how many of one thing we're looking up. If the total is 0, then it's ignored and moves onto the next column.

Sub CopyAll()

Sheets("Master Lookup").Select
    If Range("D3") = 0 Then GoTo 20
       'Perform Care Copy
        Dim cpySht As Worksheet, pstSht As Worksheet, PasteRange As Range
        Dim CopyFromRange As Range
        Dim CopyToRange As Range
          Set cpySht = Sheets("Halifax Lookup")
          Set pstSht = Sheets("Master Lookup")
          
    Sheets("Halifax Lookup").Select
    Range("H3:L3").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=5, Criteria1:="1"
    
          Set PasteRange = pstSht.Range("D65536").End(xlUp).Offset(1, 0)
        cpySht.Range("$I$4", "$I$104").Copy
    'Copys the data from the main sheet
        PasteRange.PasteSpecial Paste:=xlPasteValues
20
        If Range("E3") = 0 Then GoTo 30
       'Perform Care Copy
          Set cpySht = Sheets("Cedar City Lookup")
          Set pstSht = Sheets("Master Lookup")
    Sheets("Cedar City Lookup").Select
    Range("I3:L3").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=5, Criteria1:="1"
    
          Set PasteRange = pstSht.Range("E65536").End(xlUp).Offset(1, 0)
        cpySht.Range("$J$4:$J$104").Copy
    'Copys the data from the main sheet
        PasteRange.PasteSpecial Paste:=xlPasteValues
30
            If Range("F3") = 0 Then GoTo 40
       'Perform Care Copy
          Set cpySht = Sheets("Red Deer Lookup")
          Set pstSht = Sheets("Master Lookup")
    Sheets("Red Deer Lookup").Select
    Range("I3:L3").Select
    Selection.AutoFilter Field:=4, Criteria1:="1"
    
          Set PasteRange = pstSht.Range("F65536").End(xlUp).Offset(1, 0)
        cpySht.Range("$I$4:$I$104").Copy
    'Copys the data from the main sheet
        PasteRange.PasteSpecial Paste:=xlPasteValues
40
                If Range("G3") = 0 Then GoTo 50
       'Perform Care Copy
          Set cpySht = Sheets("Edmonton Lookup")
          Set pstSht = Sheets("Master Lookup")
          
    Sheets("Edmonton Lookup").Select
    Range("I3:L3").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=5, Criteria1:="1"
    
          Set PasteRange = pstSht.Range("G65536").End(xlUp).Offset(1, 0)
        cpySht.Range("$I$4:$I$104").Copy
    'Copys the data from the main sheet
        PasteRange.PasteSpecial Paste:=xlPasteValues
50
                If Range("H3") = 0 Then GoTo 60
       'Perform Care Copy
          Set cpySht = Sheets("Clarksville Lookup")
          Set pstSht = Sheets("Master Lookup")
          
    Sheets("Clarksville Lookup").Select
    Range("I3:L3").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="1"
    
          Set PasteRange = pstSht.Range("H65536").End(xlUp).Offset(1, 0)
        cpySht.Range("$J$4:$J$104").Copy
    'Copys the data from the main sheet
        PasteRange.PasteSpecial Paste:=xlPasteValues
60
                If Range("I3") = 0 Then GoTo 70
       'Perform Care Copy
          Set cpySht = Sheets("Welland Lookup")
          Set pstSht = Sheets("Master Lookup")
    Sheets("Welland Lookup").Select
    Range("I3:L3").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="1"
    
          Set PasteRange = pstSht.Range("I65536").End(xlUp).Offset(1, 0)
        cpySht.Range("$I$4:$I$2000").Copy
    'Copys the data from the main sheet
        PasteRange.PasteSpecial Paste:=xlPasteValues
70

End Sub

What's going on - It's not filtering out anything, it just puts the autofilter on... But the filter doesn't actually filter out, and when it does copy, it copies the range, not the filtered out information. Any ideas/Suggestions?

 

[HalfAce]

Does this pretty much do what you're after?

Sub DataFinder()
Dim DestSht As String, _
    SourceSht As Integer, _
    LstRw As Long, _
    DestCol As Integer
DestSht = "Sheet1"

For SourceSht = 2 To 4
  With Sheets(SourceSht)
    LstRw = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Columns("L:L").AutoFilter Field:=1, Criteria1:="1"
    Select Case SourceSht
      Case 2
        DestCol = 4
      Case 3
        DestCol = 5
      Case 4
        DestCol = 6
    End Select
    .Range("I2:I" & LstRw).SpecialCells(xlCellTypeVisible).Copy _
      Sheets(DestSht).Cells(Rows.Count, DestCol).End(xlUp)(2)
    .AutoFilterMode = False
  End With
Next

End Sub

[EDIT:]
Sorry, been kinda busy here and posted that code before I saw yours.
Give me a chance to go through what your's is doing and see if what I just posted still applies.

 

[enigmahack]

yeah, I just checked - I'm still pretty much a beginner at VB, but tried to get your code to work - it looks like it's taking information all from the same sheet with the same pages. Not that this isn't right, but I think I wasn't clear enough with what I was trying to do exactly?

I have several pages, and I'm looking to retrieve information from pages 2, 3, 4, etc... and put it on page 1, in the appropriate column.

The way I have page 1 laid out is like this:

Row1 and 2 are all used for formatting, Row 3 is the Title of the different "sites" I'm getting information for, and then Row 4 is where the data is supposed to start. Column A, B, and C are used for Formatting, and Column D is where the data starts.

Column D on page 1 would be for information from Page 2. (a single column of data)
Column E on page 1 would be for information from Page 3, etc..

How those data sheets are set up:

There are lists of employees. I've got some Vlookup's that validate if they're still active or not. If they ARE active, a number 1 appears in Column L on all data sheets *2, 3, 4, etc*

If there is a 1 in the L column, I'd like to copy just 1 cell within that same row over to page 1. *The cell changes on the different sheets, some pages it's the J column, some it's the I column*

The other thing is this: On the main page, there is a "Countif" function that counts the number of times 1 shows up *to say the reps are employed* per page. If the number is 0, I don't want it to copy anything over. If it's 1 or above, I want it to copy that 1 employee's info over, 2, 3, 4, etc...

That's kind of what I'm going for - I thought that I'd be able to do it with autofilter, and I can get the filter however it doesn't actually FILTER anything out - it just puts the filter in place without segregating the information.

Then, if I do the filter manually, when I try and get it to copy the information, it doesn't do that either - It'll copy a specific range, but unfortunately it doesn't select JUST the ones that have the 1 beside them.

This is where my connundrum exists - I think I kind of get where you were going with your code, but it's not working as I'm substituting things in like sheets, etc..

Thanks so much for your help though - it's greatly appreciated!

 

[HalfAce]

OK, I've only got a couple of questions now.

*The cell changes on the different sheets, some pages it's the J column, some it's the I column*

Does this mean the column to copy from is going to be either column I or column J? - or does it mean the colimn with the filter criteria (the #1) is going to be in either column I or J? (And in either case, how do we determine which column we're interested in for each sheet?)

The other thing is this: On the main page, there is a "Countif" function that counts the number of times 1 shows up *to say the reps are employed* per page. If the number is 0, I don't want it to copy anything over. If it's 1 or above, I want it to copy that 1 employee's info over, 2, 3, 4, etc...

Am I right in thinking on the "Master Lookup" sheet, cell D3 tells us if we need to worry about sheet2, cell E3 tells us if we need to worry about sheet3, etc.?

I'm fixing to leave here in a few minutes but I'll see if I can take this up again when I get home.
Have no fear, we'll get it figured out and it'll work slicker'n snot. (And you'll learn a bit more about vba in the process.) :wink:

I'll be back soon.

 

[enigmahack]

OK, I've only got a couple of questions now.

*The cell changes on the different sheets, some pages it's the J column, some it's the I column*

Does this mean the column to copy from is going to be either column I or column J? - or does it mean the colimn with the filter criteria (the #1) is going to be in either column I or J? (And in either case, how do we determine which column we're interested in for each sheet?)

The other thing is this: On the main page, there is a "Countif" function that counts the number of times 1 shows up *to say the reps are employed* per page. If the number is 0, I don't want it to copy anything over. If it's 1 or above, I want it to copy that 1 employee's info over, 2, 3, 4, etc...

Am I right in thinking on the "Master Lookup" sheet, cell D3 tells us if we need to worry about sheet2, cell E3 tells us if we need to worry about sheet3, etc.?

I'm fixing to leave here in a few minutes but I'll see if I can take this up again when I get home.
Have no fear, we'll get it figured out and it'll work slicker'n snot. (And you'll learn a bit more about vba in the process.) :wink:

I'll be back soon.

Okay: to your first question:

The number 1 is always going to be in the same column (Column L)
The information we want to copy changes from sheet to sheet (most are I, I think one or two are column J)
For your second comment: You're correct - D3 will tell us if we need to look at page 2 or not. E3 will tell us if we need to look at page 3 or not, etc.. You're on the right track there.

The idea being: If there's no active employees in "Halifax Lookup" then we don't need to get data from there.

So yes, you've got the right idea

Thanks again - I'm definitely interested in seeing how you would approach this - Always up for a learning experience

 

[HalfAce]

The information we want to copy changes from sheet to sheet (most are I, I think one or two are column J)

How can we determine (without hard coding the column(s)) which column to copy from on any given sheet?

 

[enigmahack]

Well, I suppose that there certainly IS a way if needed - Perhaps hiding a character in the column in row 1 is a solution? There's certainly space for it!