Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help. Select dynamic workbooks.
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup in different workbooks with dynamic ranges | Excel Worksheet Functions | |||
Copying from dynamic workbooks | Excel Programming | |||
Select dynamic range | Excel Programming | |||
dynamic worksheet select | Excel Programming | |||
select dynamic range with dynamic start point | Excel Programming |