Dynamic Workbook in VBA

M

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 I originally started with:

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?
FYI, 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.

I posted this elsewhere are some took a stab at it with the following:
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

This doesn't quite work, but I have a feeling it is close to it.
Anyone have any ideas?

Any help/direction is much appreciated!
 
J

Jim Rech

If you always start with the target workbook active then your first line can
be:

Set TargerWB = ActiveWorkbook

I used TargetWB instead of overbk just to make it generic. It doesn't
matter what name you use. Replace all uses of overbk with whatever you use.

You don't need the ChDir for the macro to run. Does not harm though.

I didn't run your code but it looks okay except for the replace:

overbk.Sheets("Means").Cells.Replace _

It helps to know what doesn't work. Why not say it?

--
Jim
| 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 I originally started with:
|
| 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?
| FYI, 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.
|
| I posted this elsewhere are some took a stab at it with the following:
| 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
|
| This doesn't quite work, but I have a feeling it is close to it.
| Anyone have any ideas?
|
| Any help/direction is much appreciated!
 
M

matthewc81

If you always start with the target workbook active then your first line can
be:

Set TargerWB = ActiveWorkbook

I used TargetWB instead of overbk just to make it generic. It doesn't
matter what name you use. Replace all uses of overbk with whatever you use.

You don't need the ChDir for the macro to run. Does not harm though.

I didn't run your code but it looks okay except for the replace:

overbk.Sheets("Means").Cells.Replace _


It helps to know what doesn't work. Why not say it?

--

| 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 I originally started with:
|
| 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?
| FYI, 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.
|
| I posted this elsewhere are some took a stab at it with the following:
| 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
|
| This doesn't quite work, but I have a feeling it is close to it.
| Anyone have any ideas?
|
| Any help/direction is much appreciated!
Click to expand...

Sorry about not including the error. What happens when I hit the run
button, I get a pop-up window that says Run-time error '438': Object
doesn't support this property or method.
When I click the Debug button the line
allbk.Selection.CurrentRegion.Select
is highlighted.

Also, should the Set TargerWB = ActiveWorkbook command replace the
set overbk = workbooks("overall.xls") command or just get inserted
in front of it?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA help. Select dynamic workbooks. 5
Help making this macro dynamic :) 1
cannot insert row in vba 1
Find copy data from a cell in Personal workbook and copy it to a cellin current workbook 14
Problem sorting a range of columns using VBA 1
looping macros 3
Activating a workbook 1
Dynamic Array not response 5

Top