Solved

Dynamically changing Scroll area in Excel using VBA

Posted on 2010-08-29
6
653 Views
Last Modified: 2012-05-10
Hi,
I am having some difficulty dynamically changing the scroll area of my worksheets. I copy a range from one sheet to another and then I want to limit the scroll area of the final sheet. any help would be appreciated.

Thanks,
scurvylion
Sub DetailCalculations()



Dim wkmc As Worksheet, wkss As Worksheet

Dim rngLast As Range, rng As Range

Dim lrow As Long, lastrow As Long, endrow As Long, endtbl As Range



Set wkmc = Worksheets("ModelCalculations")

Set wkss = Worksheets("Menu Detail Data")

Set rngLast = wkmc.Range("A1").End(xlDown)

lrow = rngLast.Row

lastrow = lrow + 1

endtbl = lastrow + 9



    endrow = wkss.Range("A9").SpecialCells(xlCellTypeLastCell).Row

    wkss.Range(Cells(9, 1), Cells(endrow, 20)).Clear



     wkmc.Range("A1", wkmc.Cells(lastrow, 20)).Copy

    

  With wkss.Range("A9")

     .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

     .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

   end with



wkss.scrollarea = wkss.Range(Cells(1,1), Cells(endtbl, 20))



End Sub

Open in new window

0
Comment
Question by:scurvylion
  • 3
  • 3
6 Comments
 

Author Comment

by:scurvylion
ID: 33554478
Sorry, I forgot to mention that the line

wkss.scrollarea = wkss.Range(Cells(1,1), Cells(endtbl, 20))

gives me a run-time error 13: type mismatch

I've tried modifying it in multiple different ways but it still gives me an error although the error may be different than the one mentioned above.

The direct reference

wkss.ScrollArea = ("A1:T1900")

works fine but I would like to be able to dynamically control the scroll area.

Thanks

0
 
LVL 12

Expert Comment

by:tilsant
ID: 33555981
Try this one....

I have used:
wkss.scrollarea = "A1:" & Chr(64 + endtbl) & 20



Tils
Sub DetailCalculations()

Dim wkmc As Worksheet, wkss As Worksheet
Dim rngLast As Range, rng As Range
Dim lrow As Long, lastrow As Long, endrow As Long, endtbl As Long 'dim of endtbl changed to Long

Set wkmc = Worksheets("ModelCalculations")
Set wkss = Worksheets("Menu Detail Data")
Set rngLast = wkmc.Range("A1").End(xlDown)
lrow = rngLast.Row
lastrow = lrow + 1
endtbl = lastrow + 9

    endrow = wkss.Range("A9").SpecialCells(xlCellTypeLastCell).Row
    wkss.Range(Cells(9, 1), Cells(endrow, 20)).Clear

     wkmc.Range("A1", wkmc.Cells(lastrow, 20)).Copy
    
  With wkss.Range("A9")
     .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   end with

'ScrollArea changed
wkss.scrollarea = "A1:" & Chr(64 + endtbl) & 20
End Sub

Open in new window

0
 
LVL 12

Accepted Solution

by:
tilsant earned 500 total points
ID: 33556046
My mistake!!
I got confused with the row and column numbering!

It should be this way..
wkss.scrollarea = "A1:T" & endtbl


Revised Code attached.
Tils.
Sub DetailCalculations()

Dim wkmc As Worksheet, wkss As Worksheet
Dim rngLast As Range, rng As Range
Dim lrow As Long, lastrow As Long, endrow As Long, endtbl As Long 'dim of endtbl changed to Long

Set wkmc = Worksheets("ModelCalculations")
Set wkss = Worksheets("Menu Detail Data")
Set rngLast = wkmc.Range("A1").End(xlDown)
lrow = rngLast.Row
lastrow = lrow + 1
endtbl = lastrow + 9

    endrow = wkss.Range("A9").SpecialCells(xlCellTypeLastCell).Row
    wkss.Range(Cells(9, 1), Cells(endrow, 20)).Clear

     wkmc.Range("A1", wkmc.Cells(lastrow, 20)).Copy
    
  With wkss.Range("A9")
     .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   end with

'ScrollArea changed
wkss.scrollarea = "A1:T" & endtbl
End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:scurvylion
ID: 33558199
Awesome job!

Thanks bro. That works great - I think I must have tried every combination but that one.

scurvylion
0
 

Author Closing Comment

by:scurvylion
ID: 33558209
Thanks again - much appreciated!
0
 
LVL 12

Expert Comment

by:tilsant
ID: 33559255
Thanks for the Grade, Scurvylion! :)


Tils.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now