For Each Sheet in Workbook...........

[riceprincess]

Im writing a code which lookings through each sheet in a workbook… and if the value in column A is "In Progress", then I need that entire row copied and pasted into the "master" sheet and then continue looking down the row… and then onto the next sheet..

I'm not even sure where to start. Can someone help?

 

[CautionMP]

Assuming that the 'Master' sheet is in the same workbook and that it has the same number of columns as the other sheets.

Sub MoveInProgress()
'Change this to the real name of your 'Master' worksheet
Const cstMasterWorksheetName = "Master"
Dim wksSheet As Worksheet, wksMaster As Worksheet
Dim lngInputRow As Long, lngOutputRow As Long, lngColumnIndex As Long
Dim strColumnA As String

'Get the 'Master' worksheet and find the last row & column
Set wksMaster = Worksheets(cstMasterWorksheetName)
lngOutputRow = wksMaster.UsedRange.Rows.Count
lngColumnIndex = wksMaster.UsedRange.Columns.Count

'Cysle through all the worksheets in the workbook
For Each wksSheet In Worksheets
  'reset input row for each sheet
  lngInputRow = 0
  'Skip the 'Master' sheet
  If wksSheet.Name <> cstMasterWorksheetName Then
    Do
      'Index the row in the input sheet
      lngInputRow = lngInputRow + 1
      'Grab the value in column A to test
      strColumnA = wksSheet.Cells(lngInputRow, 1)
      'Test for 'In Progress' and copy row if it exists
      If strColumnA = "In Progress" Then
        'Move to the new row in the output sheet
        lngOutputRow = lngOutputRow + 1
        'Cycle through all the columns in this row
        For lngColumnIndex = 1 To lngColumnIndex
          wksMaster.Cells(lngOutputRow, lngColumnIndex) = wksSheet.Cells(lngInputRow, lngColumnIndex)
        Next lngColumnIndex
      End If
    Loop Until strColumnA = ""
  End If
Next wksSheet
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[riceprincess]

Thanks soo much!

So this indexes all the rows with "in progress" in column A.... I still have to do a copy paste somewhere?

 

[riceprincess]

Also... If the "in progress" is a result of a choice in a dropdown menu... is it still considered a string?

 

[CautionMP]

[ol][li]This little chunk here actually copies the row (one cell at a time), so no you don't need to do a copy/paste.

[green]'Cycle through all the columns in this row[/green]
        For lngColumnIndex = 1 To lngColumnIndex
          wksMaster.Cells(lngOutputRow, lngColumnIndex) [b]=[/b] wksSheet.Cells(lngInputRow, lngColumnIndex)
        Next lngColumnIndex

[/li]
[li]If the dropdown value is tied to the cell, yes, it's considered a string.[/li][/ol]

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[riceprincess]

If the "in progress" part is in column B (not A).. do i just change it to wksSheet.Cells(lngInputRow, 2)..
hmm... when i F8 the code to run through it.. it never gets past "If strColumnA = "In Progress" Then".. just loops back to the Do part...

 

[CautionMP]

Yes [tt]strColumnA = wksSheet.Cells(lngInputRow, 2)[/tt] will check column 'B'.

Are the dropdown box values (I assume there are more than one) tied to underlying cells or are they just drawn over column 'B'?

(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

P.S. If the dropdown boxes are tied to the cell in Column B you will need to change the code to reference the number (ordinal) returned by the dropdown box. (i.e. if the box has three values in it and "In Progress" is the second item you will need to change the code to look for "2".)

...
'Test for 'In Progress' and copy row if it exists
If strColumnA = [b]2[/b] Then
...

(GMT-07:00) Mountain Time (US & Canada)

 

[riceprincess]

i honestly don't know what underlying cells are.

each cell (for each sheet other than the master) starting from on column B4 has a drop down menu attached to it with 4 options... "in progress" is on of the options.

 

[riceprincess]

the dropdown is all down column B... so B4, B5, B6 etc...

 

[CautionMP]

Sorry, I should say Cell Link, not underlying cell.

If you right click on one of the DropDown boxes and select [tt]Format Control[/tt] then navigate to the [tt]Control[/tt] tab on the resulting dialog box, is there anything listed for the [tt]Cell Link:[/tt] box?

(GMT-07:00) Mountain Time (US & Canada)

 

[riceprincess]

I'm sorry.. I'm really slow..........

the dropdown box is created using the Validation command under the Data menu in EXCEL and the choices just refer to a range that i put in some range way down in column T which is no where near the data im looking at.

 

[CautionMP]

It has nothing to do with slow, you know what your worksheet looks like, I'm just guessing.

Let's make another change to the code:

[i][gray]...
      strColumnA = wksSheet.Cells(lngInputRow, 2)[/gray][/i]
[b]      Debug.print wksName, "Row : " & lngInputRow, strColumnA[/b]
[i][gray]      'Test for 'In Progress' and copy row if it exists
...[/gray][/i]

Now Step Into (F8) the code and look at what prints out in the Immediate window, hopfully it's not blank. If not it should shed some light on what changes we need to make in the code.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[riceprincess]

is wksName a string?

I have option explicit going on

 

[CautionMP]

Sorry, [tt]wksSheet.Name[/tt].

This way you can tell what sheet & row the value is coming from in case you get an unexpected result.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[riceprincess]

I can see the string.. it never says "in progress".. the thing is... nothing is pasting or appearing in the master sheet... as though it never finds "in progress

 

[Gavona]

Just a thought..you could have filtered (autofilter) each sheet to display the info you were interested in and then looped through copying the visible cells. Much easier to see what is going wrong and probably quicker to use in built functionality of Excel.




Gavin

 

[CautionMP]

Ok, Step Into (F8) the code again, after the code passes[tt]
lngOutputRow = wksMaster.UsedRange.Rows.Count[/tt]
Hover you mouse over [tt]lngOutputRow[/tt] and see what value it shows
-or-
add the line [tt]Debug.Print lngOutputRow[/tt] after the above line to determine what row the copying is starting on.

I suspect the code may be adding the lines to the master sheet 10's/100's/1000's of rows down.

CMP

(GMT-07:00) Mountain Time (US & Canada)