[RESOLVED] Looping through sheets problems and setting of variables - vba
I am doing this simple routine - checking for values that contain prefix IK column A in all sheets except the IK ouput which is the ouput sheet only.
I am having difficulty setting the varaible in the correct way and getting it to work.
Please see comments which explains some of the difficulties.
Can anyone help debug??
VB Code:
Sub IKlist()
Dim wksSheet As Worksheet
Dim i As Integer
Dim thiscell As String
Dim Filelength As Integer
Dim obook As Workbook
Dim xlapp As Excel.Application
Dim j As Integer
' CREATE OUTPUT SHEET IK Output
'Count Worksheets
sheetcount = Worksheets.Count
'Add worksheets after the last sheet
Worksheets.Add After:=Sheets(sheetcount)
'Count Worksheets
sheetcount = Worksheets.Count
'Name the sheet and add the number of sheets
Worksheets(sheetcount).Name = "IK output"
'set varaibles to work with the active workbook and any sheet except the ouput sheet
Set xlapp = Excel.Application 'work with active excel application
Set obook = xlapp.ActiveWorkbook 'work with active workbook - not specific
Set wksSheet = ?????????????? 'set wksheet to be any worksheet in the active workbook. This is because I am looping through each worksheet
' Loop through each worksheet in active workbook - not a specific work book- looking for values that contain prefix IK and copying it to the IK output sheet
' The output sheet is the ouput sheet only - therefore we should not loop through this sheet - its only used for poulating results
Re: Looping through sheets problems and setting of variables - vba
A couple of suggestions:
You can set wksSheet to be Worksheets(1) - there is always a first sheet in any workbook.
If you move the line: Filelength = wkssheet.usedrange... inside the For loop then it will recalculate this for each sheet, which presumably is what you need....
Re: Looping through sheets problems and setting of variables - vba
I have made the changes below - it runs for about 2 seconds but then stops. don't think its looping through any of the sheets, whats wrong buddy??
All its doing at mow is creating the sheet.
Also the sheet IK ouput gets created previously - its not meant to loop to this sheet - this is the output only.
VB Code:
Sub IKlist()
Dim wksSheet As Worksheet
Dim i As Long
Dim thiscell As String
Dim Filelength As Integer
Dim obook As Workbook
Dim xlapp As Excel.Application
Dim j As Long
' CREATE OUTPUT SHEET IK Output
'Count Worksheets
sheetcount = Worksheets.Count
'Add worksheets after the last sheet
Worksheets.Add After:=Sheets(sheetcount)
'Count Worksheets
sheetcount = Worksheets.Count
'Name the sheet and add the number of sheets
Worksheets(sheetcount).Name = "IK output"
Set xlapp = Excel.Application 'work with active excel application
Set obook = xlapp.ActiveWorkbook 'work with active workbook - not specific
Set wksSheet = obook.Worksheets(1) 'set wksheet to be any worksheet in the active workbook. This is because I am looping through each worksheet
' Loop through each worksheet in active workbook - not a specific work book- looking for values that contain prefix IK and copying it to the IK output sheet
Re: Looping through sheets problems and setting of variables - vba
ska67can's code may work, you could also try the following which has
been modified from your code..
Notes
I am assumeing that you are running this in the Workbook you want to
have the results displayed in. Hence I have added ThisWorkbook. around the place.
Re: Looping through sheets problems and setting of variables - vba
I need to keep it or use it after I run my code.
I just need an output sheet - don't think the worksheet code is working as well - because I added the sheet manually and then run just the sheet element - but its not working.:
When I hover over the this ThisWorkbook below, it says object variable or with block variable not set.
Re: Looping through sheets problems and setting of variables - vba
Originally Posted by gphillips
The below error apprears in the line in bold below.
Run-time error 1004.
Cannot rename a sheet to the same name as another sheet, a referenced object library or
a workbook referenced by visual basic
I think that the problem is occurring because the code that I wrote did not check to see if the Worksheet already existed.
The function below will check to see if the named worksheet already exists.
Note : I convert the name of the worksheet I am testing and the name it is being
checked against to Upper Case as the Worksheet names in Excel are not
Case Sensistive.
VB Code:
Function WkShtExists(WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
Dim WkSheet As Worksheet
WkShtExists = False
For Each WkSheet In ThisWorkbook.Worksheets
If UCase(WName) = UCase(WkSheet.Name) Then ' Worksheet Names are not Case Sensitive
WkShtExists = True
Exit For
End If
Next WkSheet
End Function
Using this we can then modify your IKList sub, although You will need to
decide how you want to handle the situation.
In the modified code below I am assuming that you want to clear the contents of the IK Output sheet if it already exists.
Re: Looping through sheets problems and setting of variables - vba
It does not work on the attached sheet - there can be huge blank gaps between the next IK Code - on even no IK- prefix on one sheet but some on another.
In this case you should find IK- prefix data on the 3rd and 4th sheet from end.
But it could any random place in column A.
I have not put the code in the attached - but just tried you code.
I am woking with massive spreadsheets.
Re: Looping through sheets problems and setting of variables - vba
I created a Module in your spreadsheet, and then pasted my code in, when I
ran it I ended up with an output sheet with 811 entries, the first four of
which where :-
Re: Looping through sheets problems and setting of variables - vba
Just to check,
Do you mean that you have put the macro into a spreadsheet called personal.xls
but are trying to run it for data in another spreadsheet, i.e. ResultsData.xls
Re: Looping through sheets problems and setting of variables - vba
Hia,personal.xls is not a spreadsheet, it is the Project explorer tree structure.
VBAProject (Personal).xls.
This is a global macro storage place where macros are stored on C Drive . It is the defualt place for global macros to be stored.
It is also should be your global place - its certainly the same on my home computer by default.
I found out that this code works fine as long as you insert module in the specific VBA project workbook tree structure you want to use it on i.e.
VBAProject (Test).xls
Its does not appear to be for global macro use which might be a limitation of thisworkbook.
After attempting to run it on personal xls tree structure it seems to be have changed Microsoft excel object sub tree structure sheet names to sheet2(IK output) and IK outputss(my 2nd attempt).
I am worried about long term damage here becuase it does not seem to be removing itself - and am worried other global macros would expect i.e sheet2 to be named IK Output otherwise it will screw it up.
Help would be appreciated here.
I need this macro to be global - not specific
I need any potential problems as a result of running it globally on personal.xls to be fixed.The problems are Microsoft Excel Objects sheet names in personal xls.(If long term damage has been done I don't know).
Re: Looping through sheets problems and setting of variables - vba
As far as I am aware neither long term damage, nor short term damage will
have been done.
I do not see VBA Project (Personal.xls) in my Project tree structure.
I think that if you have a hunt around then you will probably find a
spreadsheet called Personal.xls somewhere on your C drive.
As far as turning my code into "Global" code is concerned I have come up
with two ways.
For the sake of clarity I will separate them into two different posts.
In this first one the spreadsheet is opened using Workbooks.Open and the full
path name, We then call the IKList Sub, passing just the File name, with
extension; but without any Path.
We then use this to reference the Workbook we are working on by using
Workbooks(WkBkName) in place of ThisWorkbook
VB Code:
Option Explicit
Sub Test()
Workbooks.Open "f:\test.xls"
IKlist "test.xls"
Workbooks("test.xls").Close SaveChanges:=True
End Sub
Function WkShtExists(ByVal WkBkName As String, ByVal WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
Dim WkSheet As Worksheet
WkShtExists = False
For Each WkSheet In [B]Workbooks(WkBkName)[/B].Worksheets
If UCase(WName) = UCase(WkSheet.Name) Then ' Worksheet Names are not Case Sensitive
Re: Looping through sheets problems and setting of variables - vba
In this version of the code Instead of passing over teh name of the Workbook
we pass over an actual Workbook variable which we use in place of
ThisWorkbook
VB Code:
Option Explicit
Sub Test()
Workbooks.Open "f:\test.xls"
IKlist Workbooks("test.xls")
Workbooks("test.xls").Close SaveChanges:=True
End Sub
Function WkShtExists(ByVal [B]WkBk As Workbook[/B], ByVal WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
Dim WkSheet As Worksheet
WkShtExists = False
For Each WkSheet In [B]WkBk[/B].Worksheets
If UCase(WName) = UCase(WkSheet.Name) Then ' Worksheet Names are not Case Sensitive
Re: Looping through sheets problems and setting of variables - vba
I am using Excel 2002, I cannot see a Personal.xls file at all when coding macros.
All three of the methods that I have posted work with my setup.
Unfortunately I have never heard of Excel behaving in the way that yours
does, using a Personal.xls spreadsheet.
You could try re-writing my code, removing the references to the workbooks
and sheets andreplacing them with references that you know work, like the xlapp in your example.
Other than that I am stumped, maybe someone else can offer a suggestion.
Re: Looping through sheets problems and setting of variables - vba
To try to clear things up a bit here; Personal.xls is the file Excel generates if you elect to store macros in 'Personal Macro Workbook' when you record macros. Actually, I normally store them in 'This Workbook' but there you go. It is just a workbook like any other, and loads up with Excel so that you have access to your previously saved macros when you are running other projects.
The DeleteDuplicates sub can be run from any book because it is added in. However, you will have trouble with it if you don't have a sheet named "Sheet1", because you've hardcoded it into the sub.
If you want to run the code below, don't run the Test sub. Run IKList and pass the current workbook as a parameter (ActiveWorkbook) to run it from any sheet. Although I haven't scrutinised it, it doesn't look as though there is hardcoding in there that would prevent it from running on any workbook.
If you want to run it on any xls file on your hard drive, you're going to have to open it first though. Excel can't figure out what's in the Workbook and hence take coded decisions without opening the file and having a look. You can run it in the background, so that you don't have to make the books visible, but you still have to give Excel the access.
Re: Looping through sheets problems and setting of variables - vba
Torc - thanks for the code m8- I only have to change the workbook name in the code for it to work . However, I feel I have openned up a debate with the thisworkbook code.
Ref : earlier solution coding by torc proposed using thisworkbook.
Zaza, thanks for the insight into the difference between:
why the delete duplicates code (personal.xls stored macro) works on any already previously opened spreadsheet and why the looping between different sheets code(personal.xls) does not work on an already opened spreadsheet. The looping between sheet worked if you stored the macro in a module within the specific workbook i.e. not personal.xls.
[RESOLVED] Looping through sheets problems and setting of variables - vba
Reply With Quote
Originally Posted by gphillips
