With/End With block -- move cell pointer on target workbook 1

[Cotton9]

Attempting to use your suggestion of not selecting things. Can get most to work except restoring the original activecell position on target workbook. Attempting to keep screen flashing between workbook and worksheets to a minimum. Below is a demo macro to illustrate my dilemma.

I have two workbooks 1) – Scorecard*, 2) – Projects*. Each workbook has multiple worksheets. The macro should not care what is active at runtime yet move cell pointer to a saved cell address at the end of a series of macros. Everything is hard coded in the demo to keep it simple

The first 1) workbook is a downloaded workbook generated by a mainframe. The second 2) workbook is a log book to pull specific records from first workbook.

Sub dbsel()

‘  all values are hardcoded for testing

‘ opening or target test workbook 
Windows("Scorecard-Proj-020212.xlsb").Activate 

cSCWorkBook = ActiveWorkbook.Name
cSCWorkSheet = ActiveCell.Worksheet.Name

‘  jump to any different workbook 
Windows("Projects-12-v2.xlsb").Activate

‘ when this code runs it will not know
‘ what workbook s active but needs to
‘ move cell pointer to original active cell on target workbook/sheet

With Windows(cSCWorkBook)
    With Workbooks(cSCWorkBook).Sheets(cSCWorkSheet)
        ‘ cell address is hard coded for testing
        [b].Range("A1").Select[/b]   ‘ fails with “Select method of Range class failed”
    End With
End With

End Sub

Thanks,


D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[PHV]

No error here ?
cSCWorkSheet = ActiveCell.Worksheet.Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Cotton9]

The error occurs on the line
.Range("A1").Select ' fails with "Select method of Range class

On my home PC I created the above two spreadsheet files with blank spreadsheet tabs in each. Loaded macro in Projects-12-v2.xls.

Tested on WinXP running Excel 2003. It failed on the same line with same error message as at the office.

Tried many variation to no avail.
What am I missing?

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[SkipVought]

With Windows(cSCWorkBook)
    With Workbooks(cSCWorkBook).Sheets(cSCWorkSheet)
        ' cell address is hard coded for testing[b]
        [highlight].Activate[/highlight][/b]
        .Range("A1").Select   ' fails with "Select method of Range class failed"
    End With
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Cotton9]

Yep. That works. However it also brings the workbook forward which is what I was trying to avoid.

Is there a way to move the active cell without bringing the workbook and respective worksheet forward?

The overall intent to mimimize the workbooks/tabs from flashing up on the screen as I extract information and build reports from multipe tabs.

Thanks for you excelent suggestions. I check this site whenever I get stumped (read as often).

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[SkipVought]

mimimize the workbooks/tabs from flashing

'start of process
 application.screenupdating = false


'end of process
 application.screenupdating = true

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Cotton9]

Skip,
That will work. As I re-write I'll in cooperate it. For now I'll set it up around those With blocks so I don't see the flash. Once debugging is complete will turn it on at a high level.

Will have to give you a star for the '.Activate' suggestion earlier. Was trying so hard to eliminate activate that I refused to try it --- my own worst enemy sometimes.

Thanks,

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[SkipVought]

Activate and Select methods are valid and usable methods if used properly.

The improper use is when a reference will accomplish the same thing.

Using in a the wrap-up, to position things for the user, is perfectly acceptable.

I would recommend assigning the ScreenUpdating property at the VERY beginning of any proceure of significant length, where your are writing values to a sheet. If you have a lot of formulas, you may also want to assign the Calculation property to xlManual.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Cotton9]

Skip,

Good suggestions all. Will incooperate suggestions during the re-write. The re-calculate time is miminal in this phase but when I start on the master that feeds into this project it will be required or at least a good idea.

Thanks,

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future