VBA help. Select dynamic workbooks.

[matthewc81]

HI all,

Practically zero experience with VBA, so bear with me.

I'm trying to create a macro that will update our client data.
Problem is the names of the workbooks is constantly changing. In
SPSS, we are running a huge data dump for all of our clients along
with several ratings variables for each. I wrote a macro that works
taking this data dump, formating it and pasting it into the correct
location of our overall spreadsheet. Everything works fine up to this
point. What I'd like to do is create spreadsheets for each client and
with only their applicable ratings. I have no problem going in by
hand in the overall spreadsheet, manually deleting other clients and
blank ratings then doing a save as client_xyz. The question is, when
the macro comes with spreadsheet client_xyz, is there a way to way
that spreadsheet update instead of the overall? I'd really prefer not
to have to go into each individual one and change the macro. This is
what it looks like right now:

Sub auto_open()
'
' auto_open Macro
' Macro recorded 3/6/2008 by backstation2
'

'
ChDir "M:\DATA\MERGE\0708\graphs"
Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf"
Windows("allmeans.dbf").Activate
Selection.CurrentRegion.Select
Selection.Copy
Windows("overall.xls").Activate
Sheets("Means").Activate
Range("C5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Replace What:="#NULL!", Replacement:="", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False
Windows("allmeans.dbf").Activate
Workbooks("allmeans.dbf").Close SaveChanges:=False
End Sub

The dbf file is the data dump and the overall.xls is the overall data
containing all clients. Is there maybe code that I could use to
replace the Windows("overall.xls").Activate that looks like
Windows("Current Workbook").Activate or something along those lines?

Again, almost no knowledge of VBA here, hope that all makes sense.

Thanks in advance!

P.S. something else that I didn't mention, all the client spreadsheets
will have the same data dump tab with all ratings and all clients.
The difference is a second tab that references the overall for each
resort, so no matter whose sheet its going to, it will always be going
to cell C5 in the "Means" tab.

 

[Joel]

Sub auto_open()
'
' auto_open Macro
' Macro recorded 3/6/2008 by backstation2
'

'
set overbk = workbooks("overall.xls")

ChDir "M:\DATA\MERGE\0708\graphs"
Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf"
set allbk = Activeworkbook
allbk.activesheet.CurrentRegion.copy
destination:=overbk.Sheets("Means").Range("C5")
overbk.Sheets("Means").Range("C5").Replace _
What:="#NULL!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

allbk.Close SaveChanges:=False
End Sub

 

[matthewc81]

Sub auto_open()
'
' auto_open Macro
' Macro recorded 3/6/2008 by backstation2
'

'
set overbk = workbooks("overall.xls")

ChDir "M:\DATA\MERGE\0708\graphs"
Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf"
set allbk = Activeworkbook
allbk.activesheet.CurrentRegion.copy
destination:=overbk.Sheets("Means").Range("C5")
overbk.Sheets("Means").Range("C5").Replace _
What:="#NULL!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

allbk.Close SaveChanges:=False
End Sub

Thanks for the above, but when I try to run the above I get this line
highlighted in yellow
allbk.ActiveSheet.CurrentRegion.Copy

and a pop-up thats says compile erro: Syntax error for this line
Destination:=overbk.Sheets("Means").Range("C5")

Any thoughts?

 

[Dave Peterson]

That's actually one logical line split over two physical lines:

allbk.activesheet.CurrentRegion.copy _
destination:=overbk.Sheets("Means").Range("C5")

The space underscore means that the line is continued.

 

[Joel]

Yes i left out the under_score like Dave said, but there is still a problem.
When you have to activate a chart region you need the line

Selection.CurrentRegion.Select

I made some changes, try the code now.

Sub auto_open()
'
' auto_open Macro
' Macro recorded 3/6/2008 by backstation2
'

'
set overbk = workbooks("overall.xls")

ChDir "M:\DATA\MERGE\0708\graphs"
Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf"
set allbk = Activeworkbook
allbk.Selection.CurrentRegion.Select
Selection.Copy
overbk.Sheets("Means").Range("C5").paste
overbk.Sheets("Means").Range("C5").Replace _
What:="#NULL!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

allbk.Close SaveChanges:=False
End Sub

 

[matthewc81]

Yes i left out the under_score like Dave said, but there is still a problem.
When you have to activate a chart region you need the line

Selection.CurrentRegion.Select

I made some changes, try the code now.

Sub auto_open()
'
' auto_open Macro
' Macro recorded 3/6/2008 by backstation2
'

'
set overbk = workbooks("overall.xls")

ChDir "M:\DATA\MERGE\0708\graphs"
Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf"
set allbk = Activeworkbook
allbk.Selection.CurrentRegion.Select
Selection.Copy
overbk.Sheets("Means").Range("C5").paste
overbk.Sheets("Means").Range("C5").Replace _
What:="#NULL!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

allbk.Close SaveChanges:=False
End Sub

The compiler error is gone, but an error 498, object doesn't support
this property or method on line

allbk.Selection.CurrentRegion.Select

comes up now when I run.