Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
I am trying to automate a weekly task of multiple copying approx 50 series
of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Hi Jack
could you give some more information like: - is there a pattern with which you can find the data to copy - how is the range specified where it needs to be copied to - and so on I can't make myself a picture with the information you gave so far! Cheers Carlo "jack" wrote: I am trying to automate a weekly task of multiple copying approx 50 series of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Currently the target workbook is a collected summary of each week's data and
is set up as a daily record of each individual's data. Each individual has a daily input by date (daily date row) . There are approx. 50 columns that require data input each week and these columns are not in a set order, but could be identified with a designated name or number to correspond with a range of information from the originating weekly workbook (which has a new file name each week, containing week ending date in the file name). My thinking was to associate the data from the originating weekly workbook by setting up a template (for each new weekly workbook) with named ranges that correspond to the named column in target workbook and somehow implement copy / paste operation. I'm stuck on how to set this up. I think all the macros should be in the target workbook and would be a "for /next" type method for the copy / paste of each of the 50 columns. Any help / ideas would be appreciated. Perhaps you might know of a web site with a similar example that I could review / use. "Carlo" wrote in message ... Hi Jack could you give some more information like: - is there a pattern with which you can find the data to copy - how is the range specified where it needs to be copied to - and so on I can't make myself a picture with the information you gave so far! Cheers Carlo "jack" wrote: I am trying to automate a weekly task of multiple copying approx 50 series of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Hi Jack
ok, let me get this straight: you have one worksheet, let's name it "target" and one worksheet, let's name it "source" (is there only one "source" for each week???) The data in "source" is stored in named ranges. The data in "target" is in columns which are named exactly like the ranges in "source" that's for the facts, now for the routine: (correct me if I do anything different from what you have planned) - Open Template "Source" - Data is entered into "Source" - "Source" is saved as "Source_WEEKNr" - Close "Source_WEEKNr" - Open "Target" - Find "Source_WEEKNr" via VBA - Import Ranges into Columns - Close "Target" Is that so far correct? Shouldn't be that big a problem, if you want to do it like that. But why don't you enter the data directly into "Target" Give me an answer, and we can work this thing out. Carlo "jack" wrote: Currently the target workbook is a collected summary of each week's data and is set up as a daily record of each individual's data. Each individual has a daily input by date (daily date row) . There are approx. 50 columns that require data input each week and these columns are not in a set order, but could be identified with a designated name or number to correspond with a range of information from the originating weekly workbook (which has a new file name each week, containing week ending date in the file name). My thinking was to associate the data from the originating weekly workbook by setting up a template (for each new weekly workbook) with named ranges that correspond to the named column in target workbook and somehow implement copy / paste operation. I'm stuck on how to set this up. I think all the macros should be in the target workbook and would be a "for /next" type method for the copy / paste of each of the 50 columns. Any help / ideas would be appreciated. Perhaps you might know of a web site with a similar example that I could review / use. "Carlo" wrote in message ... Hi Jack could you give some more information like: - is there a pattern with which you can find the data to copy - how is the range specified where it needs to be copied to - and so on I can't make myself a picture with the information you gave so far! Cheers Carlo "jack" wrote: I am trying to automate a weekly task of multiple copying approx 50 series of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Hi Carlo,
You are correct with the routine as you have summarized it. The reason that the "source" data doesn't go directly into the "target" is that the "source" file is in a layout that is required by another department and is forwarded to that dept. on a weekly basis. Actually, the "source" file is compiled from data coming in, on a weekly basis, from approx. 50 different individuals and automating that maybe another task I might be working on later. Yes, there is only one "source" file each week. The "target" file has weekday rows (with dates) and each column is headed with a unique name or number. I haven't modified those columns with range names. Would that be easier to handle? One thought that I had was to do a lookup in the "target" for the unique name / number column name and find the week ending date based on the "source" file name (with the week ending date in the file name). Then mutiple paste the data from "source" file. What are your thoughts? I'm still a little lost on how to work it through. I need to make the the interface as user friendly as possible and minimum user intervention as the individual that will be using it is somewhat unfamiliar with the overall workings of Excel. My thought was one "button" in the "target" that would start a macro asking for the "source" file and go on from there. What I am attempting to automate is now all done manually and I'm unsure exactly how long it takes. However, I'm confident that if I can get it to work, it will save a lot of time. Your help getting me on the right track is greatly appreciated. Jack "Carlo" wrote in message ... Hi Jack ok, let me get this straight you have one worksheet, let's name it "target" and one worksheet, let's name it "source" (is there only one "source" for each week???) The data in "source" is stored in named ranges. The data in "target" is in columns which are named exactly like the ranges in "source" that's for the facts, now for the routine: (correct me if I do anything different from what you have planned) - Open Template "Source" - Data is entered into "Source" - "Source" is saved as "Source_WEEKNr" - Close "Source_WEEKNr" - Open "Target" - Find "Source_WEEKNr" via VBA - Import Ranges into Columns - Close "Target" Is that so far correct? Shouldn't be that big a problem, if you want to do it like that. But why don't you enter the data directly into "Target" Give me an answer, and we can work this thing out. Carlo "jack" wrote: Currently the target workbook is a collected summary of each week's data and is set up as a daily record of each individual's data. Each individual has a daily input by date (daily date row) . There are approx. 50 columns that require data input each week and these columns are not in a set order, but could be identified with a designated name or number to correspond with a range of information from the originating weekly workbook (which has a new file name each week, containing week ending date in the file name). My thinking was to associate the data from the originating weekly workbook by setting up a template (for each new weekly workbook) with named ranges that correspond to the named column in target workbook and somehow implement copy / paste operation. I'm stuck on how to set this up. I think all the macros should be in the target workbook and would be a "for /next" type method for the copy / paste of each of the 50 columns. Any help / ideas would be appreciated. Perhaps you might know of a web site with a similar example that I could review / use. "Carlo" wrote in message ... Hi Jack could you give some more information like: - is there a pattern with which you can find the data to copy - how is the range specified where it needs to be copied to - and so on I can't make myself a picture with the information you gave so far! Cheers Carlo "jack" wrote: I am trying to automate a weekly task of multiple copying approx 50 series of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Hi Jack
ok, I put together a sub and i found a sub on google which helps me find named ranges. What you have to do is following: - Put both files in the same folder - make a button in Target on sheet targetsheet (name can vary, but it needs to be changed in the vba code) - copy the button-sub below into the click event of the button - copy the function below beneath the click event (i found the function by googling, and just tweaked it a little) - change the name of the workbook in the VBA code to the one you want - enter the unique names for your columns starting in B1 and proceeding to the right - click the button Sub for the button ------------------------------------------------------------------------------------------- Dim awb As Workbook Dim row As Integer Dim wb As Workbook Dim rngStr As String Set awb = ActiveWorkbook row = awb.Worksheets("targetsheet").Cells(65536, 1).End(xlUp).row + 1 'set date in first column of last row awb.Worksheets("targetsheet").Cells(row, 1).Value = Format(Now(), "yyyy-mm-dd") a = ActiveWorkbook.Path & "\source.xls" 'change the name of the workbook Set wb = Workbooks.Open(a) awb.Activate With awb.Worksheets("targetsheet") For i = 2 To .Cells(1, 256).End(xlToLeft).Column rngStr = NamedRangeExists(.Cells(1, i).Value, wb.Name) If rngStr < "" Then .Cells(row, i).Value = rngStr End If Next i End With wb.Close Set wb = Nothing End Sub ------------------------------------------------------------------------------------------- function to copy ------------------------------------------------------------------------------------------- Function NamedRangeExists(strName As String, _ Optional wbName As String) As String 'Declare variables Dim rngTest As Range, i As Long 'Set workbook name if not set in function, as default/activebook If wbName = vbNullString Then wbName = ActiveWorkbook.Name With Workbooks(wbName) On Error Resume Next 'Loop through all sheets in workbook. In VBA, you MUST specify ' the worksheet name which the named range is found on. Using ' Named Ranges in worksheet functions DO work across sheets ' without explicit reference. For i = 1 To .Sheets.Count Step 1 'Try to set our variable as the named range. Set rngTest = .Sheets(i).Range(strName) 'If there is no error then the name exists. If Err = 0 Then 'Set the function to TRUE & exit NamedRangeExists = rngTest Exit Function Else 'Clear the error Err.Clear End If Next i End With End Function ------------------------------------------------------------------------------------------- hope that gets you started. otherwise just post again. Carlo "jack" wrote: Hi Carlo, You are correct with the routine as you have summarized it. The reason that the "source" data doesn't go directly into the "target" is that the "source" file is in a layout that is required by another department and is forwarded to that dept. on a weekly basis. Actually, the "source" file is compiled from data coming in, on a weekly basis, from approx. 50 different individuals and automating that maybe another task I might be working on later. Yes, there is only one "source" file each week. The "target" file has weekday rows (with dates) and each column is headed with a unique name or number. I haven't modified those columns with range names. Would that be easier to handle? One thought that I had was to do a lookup in the "target" for the unique name / number column name and find the week ending date based on the "source" file name (with the week ending date in the file name). Then mutiple paste the data from "source" file. What are your thoughts? I'm still a little lost on how to work it through. I need to make the the interface as user friendly as possible and minimum user intervention as the individual that will be using it is somewhat unfamiliar with the overall workings of Excel. My thought was one "button" in the "target" that would start a macro asking for the "source" file and go on from there. What I am attempting to automate is now all done manually and I'm unsure exactly how long it takes. However, I'm confident that if I can get it to work, it will save a lot of time. Your help getting me on the right track is greatly appreciated. Jack "Carlo" wrote in message ... Hi Jack ok, let me get this straight you have one worksheet, let's name it "target" and one worksheet, let's name it "source" (is there only one "source" for each week???) The data in "source" is stored in named ranges. The data in "target" is in columns which are named exactly like the ranges in "source" that's for the facts, now for the routine: (correct me if I do anything different from what you have planned) - Open Template "Source" - Data is entered into "Source" - "Source" is saved as "Source_WEEKNr" - Close "Source_WEEKNr" - Open "Target" - Find "Source_WEEKNr" via VBA - Import Ranges into Columns - Close "Target" Is that so far correct? Shouldn't be that big a problem, if you want to do it like that. But why don't you enter the data directly into "Target" Give me an answer, and we can work this thing out. Carlo "jack" wrote: Currently the target workbook is a collected summary of each week's data and is set up as a daily record of each individual's data. Each individual has a daily input by date (daily date row) . There are approx. 50 columns that require data input each week and these columns are not in a set order, but could be identified with a designated name or number to correspond with a range of information from the originating weekly workbook (which has a new file name each week, containing week ending date in the file name). My thinking was to associate the data from the originating weekly workbook by setting up a template (for each new weekly workbook) with named ranges that correspond to the named column in target workbook and somehow implement copy / paste operation. I'm stuck on how to set this up. I think all the macros should be in the target workbook and would be a "for /next" type method for the copy / paste of each of the 50 columns. Any help / ideas would be appreciated. Perhaps you might know of a web site with a similar example that I could review / use. "Carlo" wrote in message ... Hi Jack could you give some more information like: - is there a pattern with which you can find the data to copy - how is the range specified where it needs to be copied to - and so on I can't make myself a picture with the information you gave so far! Cheers Carlo "jack" wrote: I am trying to automate a weekly task of multiple copying approx 50 series of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Hi Carlo,
Thanks for your work and this will give me a real good start. I'm starting now to put it together and will let you know how well I do. Thanks again Jack "Carlo" wrote in message ... Hi Jack ok, I put together a sub and i found a sub on google which helps me find named ranges. What you have to do is following: - Put both files in the same folder - make a button in Target on sheet targetsheet (name can vary, but it needs to be changed in the vba code) - copy the button-sub below into the click event of the button - copy the function below beneath the click event (i found the function by googling, and just tweaked it a little) - change the name of the workbook in the VBA code to the one you want - enter the unique names for your columns starting in B1 and proceeding to the right - click the button Sub for the button ------------------------------------------------------------------------------------------- Dim awb As Workbook Dim row As Integer Dim wb As Workbook Dim rngStr As String Set awb = ActiveWorkbook row = awb.Worksheets("targetsheet").Cells(65536, 1).End(xlUp).row + 1 'set date in first column of last row awb.Worksheets("targetsheet").Cells(row, 1).Value = Format(Now(), "yyyy-mm-dd") a = ActiveWorkbook.Path & "\source.xls" 'change the name of the workbook Set wb = Workbooks.Open(a) awb.Activate With awb.Worksheets("targetsheet") For i = 2 To .Cells(1, 256).End(xlToLeft).Column rngStr = NamedRangeExists(.Cells(1, i).Value, wb.Name) If rngStr < "" Then .Cells(row, i).Value = rngStr End If Next i End With wb.Close Set wb = Nothing End Sub ------------------------------------------------------------------------------------------- function to copy ------------------------------------------------------------------------------------------- Function NamedRangeExists(strName As String, _ Optional wbName As String) As String 'Declare variables Dim rngTest As Range, i As Long 'Set workbook name if not set in function, as default/activebook If wbName = vbNullString Then wbName = ActiveWorkbook.Name With Workbooks(wbName) On Error Resume Next 'Loop through all sheets in workbook. In VBA, you MUST specify ' the worksheet name which the named range is found on. Using ' Named Ranges in worksheet functions DO work across sheets ' without explicit reference. For i = 1 To .Sheets.Count Step 1 'Try to set our variable as the named range. Set rngTest = .Sheets(i).Range(strName) 'If there is no error then the name exists. If Err = 0 Then 'Set the function to TRUE & exit NamedRangeExists = rngTest Exit Function Else 'Clear the error Err.Clear End If Next i End With End Function ------------------------------------------------------------------------------------------- hope that gets you started. otherwise just post again. Carlo "jack" wrote: Hi Carlo, You are correct with the routine as you have summarized it. The reason that the "source" data doesn't go directly into the "target" is that the "source" file is in a layout that is required by another department and is forwarded to that dept. on a weekly basis. Actually, the "source" file is compiled from data coming in, on a weekly basis, from approx. 50 different individuals and automating that maybe another task I might be working on later. Yes, there is only one "source" file each week. The "target" file has weekday rows (with dates) and each column is headed with a unique name or number. I haven't modified those columns with range names. Would that be easier to handle? One thought that I had was to do a lookup in the "target" for the unique name / number column name and find the week ending date based on the "source" file name (with the week ending date in the file name). Then mutiple paste the data from "source" file. What are your thoughts? I'm still a little lost on how to work it through. I need to make the the interface as user friendly as possible and minimum user intervention as the individual that will be using it is somewhat unfamiliar with the overall workings of Excel. My thought was one "button" in the "target" that would start a macro asking for the "source" file and go on from there. What I am attempting to automate is now all done manually and I'm unsure exactly how long it takes. However, I'm confident that if I can get it to work, it will save a lot of time. Your help getting me on the right track is greatly appreciated. Jack "Carlo" wrote in message ... Hi Jack ok, let me get this straight you have one worksheet, let's name it "target" and one worksheet, let's name it "source" (is there only one "source" for each week???) The data in "source" is stored in named ranges. The data in "target" is in columns which are named exactly like the ranges in "source" that's for the facts, now for the routine: (correct me if I do anything different from what you have planned) - Open Template "Source" - Data is entered into "Source" - "Source" is saved as "Source_WEEKNr" - Close "Source_WEEKNr" - Open "Target" - Find "Source_WEEKNr" via VBA - Import Ranges into Columns - Close "Target" Is that so far correct? Shouldn't be that big a problem, if you want to do it like that. But why don't you enter the data directly into "Target" Give me an answer, and we can work this thing out. Carlo "jack" wrote: Currently the target workbook is a collected summary of each week's data and is set up as a daily record of each individual's data. Each individual has a daily input by date (daily date row) . There are approx. 50 columns that require data input each week and these columns are not in a set order, but could be identified with a designated name or number to correspond with a range of information from the originating weekly workbook (which has a new file name each week, containing week ending date in the file name). My thinking was to associate the data from the originating weekly workbook by setting up a template (for each new weekly workbook) with named ranges that correspond to the named column in target workbook and somehow implement copy / paste operation. I'm stuck on how to set this up. I think all the macros should be in the target workbook and would be a "for /next" type method for the copy / paste of each of the 50 columns. Any help / ideas would be appreciated. Perhaps you might know of a web site with a similar example that I could review / use. "Carlo" wrote in message ... Hi Jack could you give some more information like: - is there a pattern with which you can find the data to copy - how is the range specified where it needs to be copied to - and so on I can't make myself a picture with the information you gave so far! Cheers Carlo "jack" wrote: I am trying to automate a weekly task of multiple copying approx 50 series of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Hi Carlo,
I'm starting to work thru what you have me started on and need additional help. Questions: Is the statement: awb.Worksheets("targetsheet").Cells(row, 1).Value = Format(Now(), "yyyy-mm-dd") supposed to put the current date in worksheet last row, first column? If so, it doesn't seem work. I've named each of the approximately 50 horizontal ranges in the "source" worksheet that are to be pasted into the "target" worksheet. These need to be pasted in as a vertical range, so I realize that I will need to use the transpose function as part of the paste operation. How do I go about pasting these ranges into the "target" each week based on a week ending date from the "source"? The dates are already setup in the left column rows of the "target" and the columns are the "clock" designation. I think I can use a forumla such as: ADDRESS(MATCH((DATE(2007,10,13)-6),wedate,0),MATCH(2097,clock,0)) to establish each paste location for each range from the "source". I hope this sounds logical. I'm not understanding how the NamedRangeExists function works to paste the "source" range data into the "target". How do I get the new name for the "source" into the VBA? The "source" file name does change each week by the week ending date placed in the file name. I think that will be a request of info from the user. Thanks..... Jack "Carlo" wrote in message ... Hi Jack ok, I put together a sub and i found a sub on google which helps me find named ranges. What you have to do is following: - Put both files in the same folder - make a button in Target on sheet targetsheet (name can vary, but it needs to be changed in the vba code) - copy the button-sub below into the click event of the button - copy the function below beneath the click event (i found the function by googling, and just tweaked it a little) - change the name of the workbook in the VBA code to the one you want - enter the unique names for your columns starting in B1 and proceeding to the right - click the button Sub for the button ------------------------------------------------------------------------------------------- Dim awb As Workbook Dim row As Integer Dim wb As Workbook Dim rngStr As String Set awb = ActiveWorkbook row = awb.Worksheets("targetsheet").Cells(65536, 1).End(xlUp).row + 1 'set date in first column of last row awb.Worksheets("targetsheet").Cells(row, 1).Value = Format(Now(), "yyyy-mm-dd") a = ActiveWorkbook.Path & "\source.xls" 'change the name of the workbook Set wb = Workbooks.Open(a) awb.Activate With awb.Worksheets("targetsheet") For i = 2 To .Cells(1, 256).End(xlToLeft).Column rngStr = NamedRangeExists(.Cells(1, i).Value, wb.Name) If rngStr < "" Then .Cells(row, i).Value = rngStr End If Next i End With wb.Close Set wb = Nothing End Sub ------------------------------------------------------------------------------------------- function to copy ------------------------------------------------------------------------------------------- Function NamedRangeExists(strName As String, _ Optional wbName As String) As String 'Declare variables Dim rngTest As Range, i As Long 'Set workbook name if not set in function, as default/activebook If wbName = vbNullString Then wbName = ActiveWorkbook.Name With Workbooks(wbName) On Error Resume Next 'Loop through all sheets in workbook. In VBA, you MUST specify ' the worksheet name which the named range is found on. Using ' Named Ranges in worksheet functions DO work across sheets ' without explicit reference. For i = 1 To .Sheets.Count Step 1 'Try to set our variable as the named range. Set rngTest = .Sheets(i).Range(strName) 'If there is no error then the name exists. If Err = 0 Then 'Set the function to TRUE & exit NamedRangeExists = rngTest Exit Function Else 'Clear the error Err.Clear End If Next i End With End Function ------------------------------------------------------------------------------------------- hope that gets you started. otherwise just post again. Carlo "jack" wrote: Hi Carlo, You are correct with the routine as you have summarized it. The reason that the "source" data doesn't go directly into the "target" is that the "source" file is in a layout that is required by another department and is forwarded to that dept. on a weekly basis. Actually, the "source" file is compiled from data coming in, on a weekly basis, from approx. 50 different individuals and automating that maybe another task I might be working on later. Yes, there is only one "source" file each week. The "target" file has weekday rows (with dates) and each column is headed with a unique name or number. I haven't modified those columns with range names. Would that be easier to handle? One thought that I had was to do a lookup in the "target" for the unique name / number column name and find the week ending date based on the "source" file name (with the week ending date in the file name). Then mutiple paste the data from "source" file. What are your thoughts? I'm still a little lost on how to work it through. I need to make the the interface as user friendly as possible and minimum user intervention as the individual that will be using it is somewhat unfamiliar with the overall workings of Excel. My thought was one "button" in the "target" that would start a macro asking for the "source" file and go on from there. What I am attempting to automate is now all done manually and I'm unsure exactly how long it takes. However, I'm confident that if I can get it to work, it will save a lot of time. Your help getting me on the right track is greatly appreciated. Jack "Carlo" wrote in message ... Hi Jack ok, let me get this straight you have one worksheet, let's name it "target" and one worksheet, let's name it "source" (is there only one "source" for each week???) The data in "source" is stored in named ranges. The data in "target" is in columns which are named exactly like the ranges in "source" that's for the facts, now for the routine: (correct me if I do anything different from what you have planned) - Open Template "Source" - Data is entered into "Source" - "Source" is saved as "Source_WEEKNr" - Close "Source_WEEKNr" - Open "Target" - Find "Source_WEEKNr" via VBA - Import Ranges into Columns - Close "Target" Is that so far correct? Shouldn't be that big a problem, if you want to do it like that. But why don't you enter the data directly into "Target" Give me an answer, and we can work this thing out. Carlo "jack" wrote: Currently the target workbook is a collected summary of each week's data and is set up as a daily record of each individual's data. Each individual has a daily input by date (daily date row) . There are approx. 50 columns that require data input each week and these columns are not in a set order, but could be identified with a designated name or number to correspond with a range of information from the originating weekly workbook (which has a new file name each week, containing week ending date in the file name). My thinking was to associate the data from the originating weekly workbook by setting up a template (for each new weekly workbook) with named ranges that correspond to the named column in target workbook and somehow implement copy / paste operation. I'm stuck on how to set this up. I think all the macros should be in the target workbook and would be a "for /next" type method for the copy / paste of each of the 50 columns. Any help / ideas would be appreciated. Perhaps you might know of a web site with a similar example that I could review / use. "Carlo" wrote in message ... Hi Jack could you give some more information like: - is there a pattern with which you can find the data to copy - how is the range specified where it needs to be copied to - and so on I can't make myself a picture with the information you gave so far! Cheers Carlo "jack" wrote: I am trying to automate a weekly task of multiple copying approx 50 series of data from one workbook and paste to another workbook (not adjacent rows or columns). The source workbook file changes each week, but is the same format. The target workbook is the same each week with multiple different "paste to" ranges each week. Any suggestions on how I could implement this automation would be appreciated. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating weeky copy / paste
Hi Jack
Ok, just send me the two sheets, then we are talking about the same issues and it should be easier to figure out what's not working. I wait for your Mail Carlo "jack" wrote: Hi Carlo, I'm not sure why the code is not working, but it is not putting the date in the last row of the first column. I tried the statements in a new blank workbook and it didn't put the date in there either. I tried to figure it out without success. The "source" worksheet name will always contain a week ending date of Saturday of each week. As an example: wk ending 10-13-07.xls. I'm glad to hear that there shouldn't be a problem coding it. I'll send you the "source" testworkbook and the "target" testworkbook to show the layout I'm working with. As there is confidential info in each, I'll have to clean them before sending them just to show layout only. I'll try to get them to you in the next couple of days. Thanks again for your help. Jack "Carlo" wrote in message ... Hi Jack concerning your first question, yes the statement should put the date in the last row of the first column. How does it not work, is there an error, or just no output, or the wrong output? For your second problem: I didn't realize, that your ranges contain more than one cell. Would it be possible to send me a testworkbook with the marked ranges? so that i can see how your ranges are built up? Otherwise it is kind of hard for me to figure out how to place them in the "target" sheet. My mail is (replace xxx with my name) It won't be necessary to use any worksheet functions because we are using VBA for all these procedures. If the name of the sheet follows a certain pattern, then it should be no problem to code it, otherwise we need input by the user. e.g. if it is always the sunday date, no problem, but if it can differ depending on holidays and such, it gets a lot harder. Hope to hear from you Carlo "jack" wrote: Hi Carlo, I'm starting to work thru what you have me started on and need additional help. Questions: Is the statement: awb.Worksheets("targetsheet").Cells(row, 1).Value = Format(Now(), "yyyy-mm-dd") supposed to put the current date in worksheet last row, first column? If so, it doesn't seem work. I've named each of the approximately 50 horizontal ranges in the "source" worksheet that are to be pasted into the "target" worksheet. These need to be pasted in as a vertical range, so I realize that I will need to use the transpose function as part of the paste operation. How do I go about pasting these ranges into the "target" each week based on a week ending date from the "source"? The dates are already setup in the left column rows of the "target" and the columns are the "clock" designation. I think I can use a forumla such as: ADDRESS(MATCH((DATE(2007,10,13)-6),wedate,0),MATCH(2097,clock,0)) to establish each paste location for each range from the "source". I hope this sounds logical. I'm not understanding how the NamedRangeExists function works to paste the "source" range data into the "target". How do I get the new name for the "source" into the VBA? The "source" file name does change each week by the week ending date placed in the file name. I think that will be a request of info from the user. Thanks..... Jack "Carlo" wrote in message ... Hi Jack ok, I put together a sub and i found a sub on google which helps me find named ranges. What you have to do is following: - Put both files in the same folder - make a button in Target on sheet targetsheet (name can vary, but it needs to be changed in the vba code) - copy the button-sub below into the click event of the button - copy the function below beneath the click event (i found the function by googling, and just tweaked it a little) - change the name of the workbook in the VBA code to the one you want - enter the unique names for your columns starting in B1 and proceeding to the right - click the button Sub for the button ------------------------------------------------------------------------------------------- Dim awb As Workbook Dim row As Integer Dim wb As Workbook Dim rngStr As String Set awb = ActiveWorkbook row = awb.Worksheets("targetsheet").Cells(65536, 1).End(xlUp).row + 1 'set date in first column of last row awb.Worksheets("targetsheet").Cells(row, 1).Value = Format(Now(), "yyyy-mm-dd") a = ActiveWorkbook.Path & "\source.xls" 'change the name of the workbook Set wb = Workbooks.Open(a) awb.Activate With awb.Worksheets("targetsheet") For i = 2 To .Cells(1, 256).End(xlToLeft).Column rngStr = NamedRangeExists(.Cells(1, i).Value, wb.Name) If rngStr < "" Then .Cells(row, i).Value = rngStr End If Next i End With wb.Close Set wb = Nothing End Sub ------------------------------------------------------------------------------------------- function to copy ------------------------------------------------------------------------------------------- Function NamedRangeExists(strName As String, _ Optional wbName As String) As String 'Declare variables Dim rngTest As Range, i As Long 'Set workbook name if not set in function, as default/activebook If wbName = vbNullString Then wbName = ActiveWorkbook.Name With Workbooks(wbName) On Error Resume Next 'Loop through all sheets in workbook. In VBA, you MUST specify ' the worksheet name which the named range is found on. Using ' Named Ranges in worksheet functions DO work across sheets ' without explicit reference. For i = 1 To .Sheets.Count Step 1 'Try to set our variable as the named range. Set rngTest = .Sheets(i).Range(strName) 'If there is no error then the name exists. If Err = 0 Then 'Set the function to TRUE & exit NamedRangeExists = rngTest Exit Function Else 'Clear the error Err.Clear End If Next i End With End Function ------------------------------------------------------------------------------------------- hope that gets you started. otherwise just post again. Carlo "jack" wrote: Hi Carlo, You are correct with the routine as you have summarized it. The reason that the "source" data doesn't go directly into the "target" is that the "source" file is in a layout that is required by another department and is forwarded to that dept. on a weekly basis. Actually, the "source" file is compiled from data coming in, on a weekly basis, from approx. 50 different individuals and automating that maybe another task I might be working on later. Yes, there is only one "source" file each week. The "target" file has weekday rows (with dates) and each column is headed with a unique name or number. I haven't modified those columns with range names. Would that be easier to handle? One thought that I had was to do a lookup in the "target" for the unique name / number column name and find the week ending date based on the "source" file name (with the week ending date in the file name). Then mutiple paste the data from "source" file. What are your thoughts? I'm still a little lost on how to work it through. I need to make the the interface as user friendly as possible and minimum user intervention as the individual that will be using it is somewhat unfamiliar with the overall workings of Excel. My thought was one "button" in the "target" that would start a macro asking for the "source" file and go on from there. What I am attempting to automate is now all done manually and I'm unsure exactly how long it takes. However, I'm confident that if I can get it to work, it will save a lot of time. Your help getting me on the right track is greatly appreciated. Jack "Carlo" wrote in message ... Hi Jack ok, let me get this straight you have one worksheet, let's name it "target" and one worksheet, let's name it "source" (is there only one "source" for each week???) The data in "source" is stored in named ranges. The data in "target" is in columns which are named exactly like the ranges in "source" that's for the facts, now for the routine: (correct me if I do anything different from what you have planned) - Open Template "Source" - Data is entered into "Source" - "Source" is saved as "Source_WEEKNr" - Close "Source_WEEKNr" - Open "Target" - Find "Source_WEEKNr" via VBA - Import Ranges into Columns - Close "Target" Is that so far correct? Shouldn't be that big a problem, if you want to do it like that. But why don't you enter the data directly into "Target" Give me an answer, and we can work this thing out. Carlo "jack" wrote: Currently the target workbook is a collected summary of each week's data and is set up as a daily record of each individual's data. Each individual has a daily input by date (daily date row) . There are approx. 50 columns that require data input each week and these columns are not in a set order, but could be identified with a designated name or number to correspond with a range of information from the originating weekly workbook (which has a new file name each week, containing week ending date in the file name). My thinking was to associate the data from the originating weekly workbook |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EzPaste-xl2ppt Excel add-in, automating the copy/paste of charts anddata | Charts and Charting in Excel | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
Automating copy-pasting of data values into a new spreadsheet layo | Excel Programming | |||
Use the Loan Template to calculate bi-weeky payments | Excel Worksheet Functions | |||
automating copy and paste | Excel Worksheet Functions |