For Each worksheet? In Application.ActiveWorkbook 3

[robsimkins]

HI guys, hope all are well, I'm looking for help on a sub which moves part lists from each product sheet into a single sheet for import to our stock system. Just need to know how to Dim my worksheets to loop thru the Wkbk?

TIA for anyone who helps!

- Rob

Sub move_parts()

Dim Endcell, Startcell As Range
Dim wksht As Worksheet //<-- wrong??

Workbooks("Airware_Portfolio.xls").Activate

For Each wksht In Application.ActiveWorkbook //<-- error

Set Startcell = Range("A2")
Set Endcell = Range("H:H").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious)

Range(Startcell, Endcell).Copy
Workbooks("Airware_part_list.xls").Activate
Worksheets("Full_list").Activate

Range("A2").Insert shift:=xlShiftDown

Workbooks("Airware_Portfolio.xls").Activate

Next wksht

Columns("A:H").EntireColumn.AutoFit

ActiveWorkbook.Save

End Sub

 

[Molby]

Try:

Sub test()
For Each sht In ActiveWorkbook.Sheets
  "Your code"
Next sht
End Sub

 

[robsimkins]

Thankyou Molby, it was a pretty easy one I just couldn't figure it out! Thanks again!

 

[SkipVought]

Hi,

Just a clarification on Molby's solution.

The Sheet Object refers to ANY Workbook Object including Chart Sheets, Macro Sheets and Dialog Sheets in addition to Worksheets.

If you have any of those objects in your Workbook, but only want to reference Worksheets, then substitute Worksheets for Sheets...

Sub test()
For Each sht In ActiveWorkbook.Worksheets
  "Your code"
Next sht
End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[robsimkins]

I'm getting a problem where the sheet isn't incrementing, only the current sheet in view loops on the range copy. The number of loops is consistent with the number of sheets but the next sheet isn't activating prior to the copy. I tried "Next sht.Activate" but got an error...

Code
Sub move_parts()

Dim Endcell, Startcell As Range
Dim wksht As Worksheet

For Each sht In Workbooks("Airware_Portfolio.xls").Sheets

Set Startcell = Range("A7")
Set Endcell = Range("H:H").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious)

Workbooks("Airware_Portfolio.xls").Activate

'Here i need to initiate an incremental loop of sheets....

Range(Startcell, Endcell).Copy

Workbooks("Airware_part_list.xls").Worksheets("Full_list").Activate

Range("A2").Insert shift:=xlShiftDown

'//Loop Next sht

Next sht

Workbooks("Airware_part_list.xls").Worksheets("Full_list").Columns("A:H").EntireColumn.AutoFit

ActiveWorkbook.Save

End Sub

Any help greatly appreciated guys!

Rob

 

[GlennUK]

Hi Rob,

the quickest solution is to put

sht.Activate

after the For Each statement.

Doing a For Each does not activate each sheet, and you've coded your stuff as if it does ( no criticism intended ).

Cheers, Glenn.

 

[SkipVought]

It is preferable NOT to Activate and Select as this will affect the performance of your application.

Rather REFERENCE the sheet object with each range. For instance...

For Each sht In Workbooks("Airware_Portfolio.xls").Sheets

    Set sht.Startcell = sht.Range("A7")
    Set sht.Endcell = sht.Range("H:H").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious)
...
Next

And better yet for performance...

For Each sht In Workbooks("Airware_Portfolio.xls").Sheets
  With sht
    Set .Startcell = .Range("A7")
    Set .Endcell = .Range("H:H").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious)
  End With
...
Next

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[monagan]

I'm having a problem, but maybe it was my understanding of what the code is meant to do.

Right now I am recieving an error on:

If Not SheetExists(sWkSht) Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sWkSht

When it gets to: ActiveSheet.Name = sWkSht

It stops and says cannot rename a sheet to the same name as another sheet, a referenced object library, or workbook rferenced by Visual Basic

"We have enough youth, How about a fountain of SMART?

 

[SkipVought]

Please post ALL your code. It's important to know exactly WHERE this code is executing.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]