Possible? Dynamic copy/paste source AND destination?

[LiAD]

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

 

[Sam Wilson]

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

 

[Dave Peterson]

Check your other post.

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

 

[LiAD]

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

 

[Sam Wilson]

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]

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