Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible? Dynamic copy/paste source AND destination?
Hi,
I have a list of data contained in a series of files stored on a network. There is one file for every month, ( file name Jan, feb etc). Each file has 31 sheets (sheet names 1,2,3 etc) to record certain events every day of the month. Some days may be empty and have nothing written. There is then another file which I would like to fill automatically from this series of files per month. This new file, called Summary in which i have 5 sheets (a,b,c,d,e). I would like to find a way of automatically copying data from - Jan/1 cells d12, e12 & m12 to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan) The next day, Jan 2 i need to take the data from the same file (if its the same month) but a different sheet, Jan/2 cells d12, e12 & m12 and copy to summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively if its the 31 Jan it copies from Jan/31, the next day it needs to find a different file and sheet, Feb/1. So basically I need a way of searching through a network by date to take data from a certain folder and sheet to copy into a corresponding row by date in another folder. The only static items are the cells to copy from, (source file and sheet change) and the file name and sheet to paste to (cells to paste to increase by one per day. I have looked at Ron de Bruins site but come up blank so far. Is this possible? Thanks LiAD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible? Dynamic copy/paste source AND destination?
Try this (tweaking paths & file names etc)
Sub SummariseYear() Application.DisplayAlerts = False Dim xwb As Workbook Dim xws As Worksheet Set xwb = ActiveWorkbook Set xws = xwb.Worksheets("a") Dim wb As Workbook Dim ws As Worksheet Dim sPath As String Dim sFile(11) As String sFile(0) = "Jan.xls" sFile(1) = "Feb.xls" sFile(2) = "Mar.xls" sFile(3) = "Apr.xls" sFile(4) = "May.xls" sFile(5) = "Jun.xls" sFile(6) = "Jul.xls" sFile(7) = "Aug.xls" sFile(8) = "Sep.xls" sFile(9) = "Oct.xls" sFile(10) = "Nov.xls" sFile(11) = "Dec.xls" Dim i As Integer sPath = "C:\path\" For i = 1 To 12 Set wb = Workbooks.Open(sPath & sFile(i)) For Each ws In wb.Worksheets xws.Range("f2").Offset(j, 0).Value = ws.Range("d12").Value xws.Range("g2").Offset(j, 0).Value = ws.Range("e12").Value xws.Range("d2").Offset(j, 0).Value = ws.Range("m12").Value j = j + 1 Next ws wb.Close Next i Application.DisplayAlerts = True End Sub "LiAD" wrote: Hi, I have a list of data contained in a series of files stored on a network. There is one file for every month, ( file name Jan, feb etc). Each file has 31 sheets (sheet names 1,2,3 etc) to record certain events every day of the month. Some days may be empty and have nothing written. There is then another file which I would like to fill automatically from this series of files per month. This new file, called Summary in which i have 5 sheets (a,b,c,d,e). I would like to find a way of automatically copying data from - Jan/1 cells d12, e12 & m12 to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan) The next day, Jan 2 i need to take the data from the same file (if its the same month) but a different sheet, Jan/2 cells d12, e12 & m12 and copy to summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively if its the 31 Jan it copies from Jan/31, the next day it needs to find a different file and sheet, Feb/1. So basically I need a way of searching through a network by date to take data from a certain folder and sheet to copy into a corresponding row by date in another folder. The only static items are the cells to copy from, (source file and sheet change) and the file name and sheet to paste to (cells to paste to increase by one per day. I have looked at Ron de Bruins site but come up blank so far. Is this possible? Thanks LiAD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible? Dynamic copy/paste source AND destination?
Check your other post.
LiAD wrote: Hi, I have a list of data contained in a series of files stored on a network. There is one file for every month, ( file name Jan, feb etc). Each file has 31 sheets (sheet names 1,2,3 etc) to record certain events every day of the month. Some days may be empty and have nothing written. There is then another file which I would like to fill automatically from this series of files per month. This new file, called Summary in which i have 5 sheets (a,b,c,d,e). I would like to find a way of automatically copying data from - Jan/1 cells d12, e12 & m12 to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan) The next day, Jan 2 i need to take the data from the same file (if its the same month) but a different sheet, Jan/2 cells d12, e12 & m12 and copy to summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively if its the 31 Jan it copies from Jan/31, the next day it needs to find a different file and sheet, Feb/1. So basically I need a way of searching through a network by date to take data from a certain folder and sheet to copy into a corresponding row by date in another folder. The only static items are the cells to copy from, (source file and sheet change) and the file name and sheet to paste to (cells to paste to increase by one per day. I have looked at Ron de Bruins site but come up blank so far. Is this possible? Thanks LiAD -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible? Dynamic copy/paste source AND destination?
Hi,
It works quite well, however now that I've tested it I can see some possible difficulties with it. Apologies there is one thing i overlooked which also leads onto another issue. - In the source files, (Jan, Feb etc), I only need to copy from sheets 3 to 33. - In the source file I may have multiple OR single entries to copy for one day. If 2/3/4 events happen in one day they both need transferred to the Summary file. This means that maybe on Jan 1 i need to copy row 12 but on Jan 2 i need to copy rows 12,13&14. In days with one event rows 13 and 14 will be empty. - The last point leads onto another complication in the destination file. The entries are recorded per day, which i'd though of just having a vertical list of dates - this won't work as I dont know of how times i need to repeat each day. In one day there will only be a few entries and past entries will not be overwritten. Is it possible to have the following sequence? - the user opens the folder and fills in the entries for the day (incidentally the events which happened on day x are recorded in the file on day x+1, as in something that happens on wed is recorded on thurs). - when the source file is closed the macro runs automatically. The macro: Checks if there is any data in rows 12,13,14, if there is it copies the data, if not it closes the open file. In the case where data exists it then opens the Summary file, pastes the data into the next xx empty rows, (depending on how much there is to be copied. For the rows that it has copied it then copy pastes the date from the source file/sheet/cell C3 to the Summary/sheet (a,b,c)/cell Axx. Maybe there is a simpler way to manage it, however I am stuck with the file formats etc as they are. Thanks a lot for your help LiAD "LiAD" wrote: Hi, I have a list of data contained in a series of files stored on a network. There is one file for every month, ( file name Jan, feb etc). Each file has 31 sheets (sheet names 1,2,3 etc) to record certain events every day of the month. Some days may be empty and have nothing written. There is then another file which I would like to fill automatically from this series of files per month. This new file, called Summary in which i have 5 sheets (a,b,c,d,e). I would like to find a way of automatically copying data from - Jan/1 cells d12, e12 & m12 to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan) The next day, Jan 2 i need to take the data from the same file (if its the same month) but a different sheet, Jan/2 cells d12, e12 & m12 and copy to summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively if its the 31 Jan it copies from Jan/31, the next day it needs to find a different file and sheet, Feb/1. So basically I need a way of searching through a network by date to take data from a certain folder and sheet to copy into a corresponding row by date in another folder. The only static items are the cells to copy from, (source file and sheet change) and the file name and sheet to paste to (cells to paste to increase by one per day. I have looked at Ron de Bruins site but come up blank so far. Is this possible? Thanks LiAD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible? Dynamic copy/paste source AND destination?
Sub SummariseYear()
Application.DisplayAlerts = False Dim xwb As Workbook Dim xws As Worksheet Set xwb = ActiveWorkbook Set xws = xwb.Worksheets("a") Dim wb As Workbook Dim ws As Worksheet Dim sPath As String Dim sFile(11) As String sFile(0) = "Jan.xls" sFile(1) = "Feb.xls" sFile(2) = "Mar.xls" sFile(3) = "Apr.xls" sFile(4) = "May.xls" sFile(5) = "Jun.xls" sFile(6) = "Jul.xls" sFile(7) = "Aug.xls" sFile(8) = "Sep.xls" sFile(9) = "Oct.xls" sFile(10) = "Nov.xls" sFile(11) = "Dec.xls" Dim i As Integer Dim k As Integer sPath = "C:\path\" For i = 1 To 12 Set wb = Workbooks.Open(sPath & sFile(i)) For Each ws In wb.Worksheets If Not xws.Index < 3 Then For k = 0 To 2 If Not IsEmpty(ws.Range("D12").Offset(k, 0)) Then xws.Range("f2").Offset(j, 0).Value = ws.Range("d12").Offset(k, 0).Value xws.Range("g2").Offset(j, 0).Value = ws.Range("e12").Offset(k, 0).Value xws.Range("d2").Offset(j, 0).Value = ws.Range("m12").Offset(k, 0).Value j = j + 1 End If Next k End If Next ws wb.Close Next i Application.DisplayAlerts = True End Sub "LiAD" wrote: Hi, It works quite well, however now that I've tested it I can see some possible difficulties with it. Apologies there is one thing i overlooked which also leads onto another issue. - In the source files, (Jan, Feb etc), I only need to copy from sheets 3 to 33. - In the source file I may have multiple OR single entries to copy for one day. If 2/3/4 events happen in one day they both need transferred to the Summary file. This means that maybe on Jan 1 i need to copy row 12 but on Jan 2 i need to copy rows 12,13&14. In days with one event rows 13 and 14 will be empty. - The last point leads onto another complication in the destination file. The entries are recorded per day, which i'd though of just having a vertical list of dates - this won't work as I dont know of how times i need to repeat each day. In one day there will only be a few entries and past entries will not be overwritten. Is it possible to have the following sequence? - the user opens the folder and fills in the entries for the day (incidentally the events which happened on day x are recorded in the file on day x+1, as in something that happens on wed is recorded on thurs). - when the source file is closed the macro runs automatically. The macro: Checks if there is any data in rows 12,13,14, if there is it copies the data, if not it closes the open file. In the case where data exists it then opens the Summary file, pastes the data into the next xx empty rows, (depending on how much there is to be copied. For the rows that it has copied it then copy pastes the date from the source file/sheet/cell C3 to the Summary/sheet (a,b,c)/cell Axx. Maybe there is a simpler way to manage it, however I am stuck with the file formats etc as they are. Thanks a lot for your help LiAD "LiAD" wrote: Hi, I have a list of data contained in a series of files stored on a network. There is one file for every month, ( file name Jan, feb etc). Each file has 31 sheets (sheet names 1,2,3 etc) to record certain events every day of the month. Some days may be empty and have nothing written. There is then another file which I would like to fill automatically from this series of files per month. This new file, called Summary in which i have 5 sheets (a,b,c,d,e). I would like to find a way of automatically copying data from - Jan/1 cells d12, e12 & m12 to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan) The next day, Jan 2 i need to take the data from the same file (if its the same month) but a different sheet, Jan/2 cells d12, e12 & m12 and copy to summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively if its the 31 Jan it copies from Jan/31, the next day it needs to find a different file and sheet, Feb/1. So basically I need a way of searching through a network by date to take data from a certain folder and sheet to copy into a corresponding row by date in another folder. The only static items are the cells to copy from, (source file and sheet change) and the file name and sheet to paste to (cells to paste to increase by one per day. I have looked at Ron de Bruins site but come up blank so far. Is this possible? Thanks LiAD |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible? Dynamic copy/paste source AND destination?
Ok thanks I'll try this one. I'll need a test it quite a bit to see what its
doing. Sorry I hadn't seen ur reply before this. Thanks a lot for your help "Sam Wilson" wrote: Sub SummariseYear() Application.DisplayAlerts = False Dim xwb As Workbook Dim xws As Worksheet Set xwb = ActiveWorkbook Set xws = xwb.Worksheets("a") Dim wb As Workbook Dim ws As Worksheet Dim sPath As String Dim sFile(11) As String sFile(0) = "Jan.xls" sFile(1) = "Feb.xls" sFile(2) = "Mar.xls" sFile(3) = "Apr.xls" sFile(4) = "May.xls" sFile(5) = "Jun.xls" sFile(6) = "Jul.xls" sFile(7) = "Aug.xls" sFile(8) = "Sep.xls" sFile(9) = "Oct.xls" sFile(10) = "Nov.xls" sFile(11) = "Dec.xls" Dim i As Integer Dim k As Integer sPath = "C:\path\" For i = 1 To 12 Set wb = Workbooks.Open(sPath & sFile(i)) For Each ws In wb.Worksheets If Not xws.Index < 3 Then For k = 0 To 2 If Not IsEmpty(ws.Range("D12").Offset(k, 0)) Then xws.Range("f2").Offset(j, 0).Value = ws.Range("d12").Offset(k, 0).Value xws.Range("g2").Offset(j, 0).Value = ws.Range("e12").Offset(k, 0).Value xws.Range("d2").Offset(j, 0).Value = ws.Range("m12").Offset(k, 0).Value j = j + 1 End If Next k End If Next ws wb.Close Next i Application.DisplayAlerts = True End Sub "LiAD" wrote: Hi, It works quite well, however now that I've tested it I can see some possible difficulties with it. Apologies there is one thing i overlooked which also leads onto another issue. - In the source files, (Jan, Feb etc), I only need to copy from sheets 3 to 33. - In the source file I may have multiple OR single entries to copy for one day. If 2/3/4 events happen in one day they both need transferred to the Summary file. This means that maybe on Jan 1 i need to copy row 12 but on Jan 2 i need to copy rows 12,13&14. In days with one event rows 13 and 14 will be empty. - The last point leads onto another complication in the destination file. The entries are recorded per day, which i'd though of just having a vertical list of dates - this won't work as I dont know of how times i need to repeat each day. In one day there will only be a few entries and past entries will not be overwritten. Is it possible to have the following sequence? - the user opens the folder and fills in the entries for the day (incidentally the events which happened on day x are recorded in the file on day x+1, as in something that happens on wed is recorded on thurs). - when the source file is closed the macro runs automatically. The macro: Checks if there is any data in rows 12,13,14, if there is it copies the data, if not it closes the open file. In the case where data exists it then opens the Summary file, pastes the data into the next xx empty rows, (depending on how much there is to be copied. For the rows that it has copied it then copy pastes the date from the source file/sheet/cell C3 to the Summary/sheet (a,b,c)/cell Axx. Maybe there is a simpler way to manage it, however I am stuck with the file formats etc as they are. Thanks a lot for your help LiAD "LiAD" wrote: Hi, I have a list of data contained in a series of files stored on a network. There is one file for every month, ( file name Jan, feb etc). Each file has 31 sheets (sheet names 1,2,3 etc) to record certain events every day of the month. Some days may be empty and have nothing written. There is then another file which I would like to fill automatically from this series of files per month. This new file, called Summary in which i have 5 sheets (a,b,c,d,e). I would like to find a way of automatically copying data from - Jan/1 cells d12, e12 & m12 to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan) The next day, Jan 2 i need to take the data from the same file (if its the same month) but a different sheet, Jan/2 cells d12, e12 & m12 and copy to summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively if its the 31 Jan it copies from Jan/31, the next day it needs to find a different file and sheet, Feb/1. So basically I need a way of searching through a network by date to take data from a certain folder and sheet to copy into a corresponding row by date in another folder. The only static items are the cells to copy from, (source file and sheet change) and the file name and sheet to paste to (cells to paste to increase by one per day. I have looked at Ron de Bruins site but come up blank so far. Is this possible? Thanks LiAD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste names from a source workbook to a destination | Excel Discussion (Misc queries) | |||
Copy every 3rd cell, define destination range for paste | Excel Programming | |||
copy paste values only using destination:= | Excel Programming | |||
how to copy succeeding rows from source workbook to destination w. | Excel Programming | |||
user form to copy and paste from source to destination | Excel Programming |