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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help. Select dynamic workbooks.
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 " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help. Select dynamic workbooks.
On Mar 7, 11:19 am, Joel wrote:
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 " wrote: 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. 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help. Select dynamic workbooks.
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. wrote: <<snipped 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help. Select dynamic workbooks.
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 " wrote: On Mar 7, 11:19 am, Joel wrote: 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 " wrote: 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. 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help. Select dynamic workbooks.
On Mar 7, 1:17 pm, Joel wrote:
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 " wrote: On Mar 7, 11:19 am, Joel wrote: 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 " wrote: 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. 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? 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. |
Reply |
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 |