Copy/Paste with variables causes Rows to Unhide

[davidam]

I have been short handing much of my code and variables have been a big part of the campaign to improve efficiency. One strange thing that has happened is that the following code causes three rows to become unhidden:

copySorc.Copy copydest

where copySorc is a large range, 3 rows by 256 columns, and cpyDest is a single cell.
I guess I can just re-hide them...but it seems to really fly in the face of my efficency drive!

 

[Norie]

Must have been a pretty good efficiency drive if that's all the code.

Can you post the rest of it, if there is any?

 

[davidam]

Well here goes...there are numerous other modules this one retrieves sets of data and then loads them into a form called Input.xls...

Public Sub Load_Case() 'Once case has been selected data is loaded onto Input form
Dim cpyDest As Range, copySorc As Range, actv As Range, caseStrg as String
Application.ScreenUpdating = False
Application.EnableEvents = False
Call Open_Data 'Opens DatSet.xls
ActiveSheet.Unprotect Password:=PW
Call Set_Case_String 'finds caseStrg from either current or archived case
WorkBook("DataSets.xls").Activate
Dim Found As Range
Set Found = Columns("B").Find(what:=caseStrg, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
Found.Offset(0, -1).Select
Set actv = ActiveCell
Set copySorc = Range(actv.Offset(0, 0).Address & ":" & actv.Offset(2, 255).Address)
Call Open_Inputs
ActiveSheet.Unprotect Password:=PW
Set cpyDest = Workbooks("Input.xls").Worksheets("Data Input").Range("A1:IV3")
Application.EnableEvents = False
copySorc.Copy cpyDest
Call PlaceData2 ' Module: PlaceData
Call Hide_Show_Rows 'Module: CS_Rows
Call Button_Values 'Module Buttons Reset
Range("C5").Value = Range("B5").Value
Range("Q31").Select
ActiveWindow.ScrollRow = 19
Call Close_Data
Workbooks("Input.xls").Activate
Application.EnableEvents = True
ActiveSheet.Protect Password:=PW
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Call Look_For_CCharts 'Determines which books need to be closed and then closes them
End Sub

 

[Norie]

There's a few things in there that could perhaps be something to do with the problem.

The main thing is the unqualified references.

For example there's no worksheet (or workbook) reference here for Columns.

Set Found = Columns("B").Find(what:=caseStrg, LookIn:=xlValues, lookat:=xlWhole)

Without a worksheet reference then VBA will look at column B of whatever worksheet it regards as active.

That might be the one you want, it might not.

If it's not then you could end up with strange behaviour, especially since it looks like that part of the code is quite important.

Not 100% sure how to fix it but it might be an idea to create some references to the workbook(s)/worksheet(s) involved.

For the workbook you are opening you could try this.

Dim wbData As Workbook
 
' code to open workbook
 
' set reference to workbook
Set wbData = Workbooks("DataSets.xls")

That reference can then be used later in the code:

Set Found = wbData.Worksheets("Sheet1").Find(what:=caseStrg, LookIn:=xlValues, lookat:=xlWhole)

If Found Is Nothing Then Exit Sub
 
Set actv = Found.Offset(0,-1)
 
Set copysource = actv.Resize(3,256)

Obviously I've just used Sheet1 because I don't know the worksheet name, so change that.

 

[davidam]

Yes, I think that is an excellent suggestion...I had the code getting lost unexpectedly before I started using the variables...why leave anything to chance?
Thanks!
David

 

[davidam]

The .Find line will not let you specify wkbk.Columns("B") but it will allow wkbk.Range("B1:Bxxx")...so this works well.

 

[Norie]

I made a mistake in the code.

After talking about the advantages of adding references, what did I miss? A worksheet reference.

This should work, with the appropriate names changed, and I've thrown in an extra reference for the range to look in as well.

Dim wbData As Workbook
Dim ws As Worksheet
Dim rngLookIn As Range
Dim rngFound As Range
Dim caseStrg As String
 
' code to open workbook
 
' set reference to workbook
Set wbData = Workbooks("DataSets.xls")
 
Set ws = wbData.Worksheets("Sheet1")

Set rngLookIn = ws.Range("B:B")

Set rngFound = rngLookIn.Find(What:=caseStrg, LookIn:=xlValues, lookat:=xlWhole)

Range("B1:Bxx") will work with a workbook but I wouldn't recommend it.

It might be fine if there is only one worksheet in the workbook but even then it's pretty straightforward to create a worksheet reference and use that.

 

[davidam]

Yes...looks excellent. Thank you!