Insert row into group of sheets

[Fred Davis]

Hi,

Can anyone spot the error in the following code snippet?

What I want to do is insert a row in a particular place in
a group of sheets - this works from the keyboard but not
from this code:
<<<<<->>>>>
Dim sWard, sSheetName, sAddress, sFormula As String
Dim iAnswer, iWardTotalRow As Integer

' Where are we?
sSheetName = ActiveSheet.Name
sAddress = ActiveCell.Address

' Get the name of the new ward
Do
Beep
sWard = InputBox("Enter name of new Ward or 'Q' to
quit", "New Ward")
Loop Until Len(sWard) > 0
If UCase(sWard) = "Q" Then
iAnswer = MsgBox("Operation cancelled", vbOKOnly)
Exit Sub
End If

Sheets(Array("ACCESS", "GCSE", "TIM
GILBERT", "ART", "BASIC EDUCATION", "Non ESOL", _
"ESOL", "COMM & PROF", "COM LEARN NET", "FAMILY
LEARNING", "FOOD & FLORISTRY", _
"HEALTH &
FITNESS", "ICT", "LANGUAGES", "TEXTILES", "WEFA", "CORPORAT
E")).Select
Sheets(sSheetName).Activate
Range(sAddress).Select
Selection.EntireRow.Insert
<<<<<->>>>>

All it does is insert a row in the active sheet.

Thanks for your help.

Fred

 

[Jim Rech]

Can anyone spot the error in the following code snippet?

For Group mode actions to affect all sheets they have to be done via the
keyboard. So in a macro you'd have to do each sheet individually.

Many people think that the above creates 4 string variables. It actually
creates 3 variants and 1 string. Unlike C and some other languages, each
variable has to be individually declared as a type:

Dim sWard as String, sSheetName as String, sAddress as String, sFormula As
String

 

[Shah Shailesh]

Hi Fred,
For grouped sheet use activewindow & selectedsheets
property. See more details in VBA help. So try this.

Replace your line
Selection.EntireRow.Insert
with below code.

dim sht as worksheet
For Each sht In ActiveWindow.SelectedSheets
sht.Range(sAddress).EntireRow.Insert
Next sht

Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

 

[Fred Davis]

Including me Jim.

Thanks,
Fred

-----Original Message-----

Many people think that the above creates 4 string variables. It actually
creates 3 variants and 1 string. Unlike C and some other languages, each
variable has to be individually declared as a type:

Dim sWard as String, sSheetName as String, sAddress as String, sFormula As
String

--
Jim Rech
Excel MVP


.