Please help

[LiAD]

Hi,

I have tried posting this several times, (the last time i put 3 up by
accident), but I have not had any response. At the moment i do not know
whether this is impossible, too difficult or just if its too hard to
understand what I need. Please let me know which it is if u can.

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 a Summary file which I would like to fill automatically from
the 12 source files (Jan, Feb etc). In Summary i have 5 sheets (a,b,c,d,e). I
would like to find a way of automatically copying data

from the source file - Jan/1 cells d12, e12 & m12
to the Summary file - cells f2, g2 & d2

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 and copy to summary/a/the next
series of empty cells. 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 to copy the
data from.

- In the source files, (Jan, Feb etc), I only need to copy from sheets 3 to
33 (there are two data sheets then one for each day of the month (sheets
1,2,3 etc)).
- 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 Summary. 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.

Is this possible?

Thanks a lot for your help
LiAD

 

[Sam Wilson]

Hi,

I've posted this to your other threads:

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

 

[Joel]

Sam: You can get the filename easily using the modification below.

For i = 1 To 12
FName = format(dateserial(2009,i,1),"mmm.xls")
Set wb = Workbooks.Open(sPath & FName)

 

[Sam Wilson]

Hi,

Yep I know - that code's from an earlier answer where the sheet names
weren't specified. I just put in 12 strings to give the OP the idea in case
there wasn't a pattern. I subsequent re-postings they've adopted my
convention!

 

[LiAD]

Thanks I'll give this a test and see what its doing.

Sorry for not seeing ur reply sooner.

Thanks a lot for your help and sticking with it
LiAD

 

[LiAD]

Hi again,

Just to let u know that I haven't forgotten about this. I'm still trying to
figure out what this is doing. It is not copying the data I need at the
moment and I can't see what is copying.

What I can see what is happening/not happening I'll let you know.

Thanks
LiAD

 

[Sam Wilson]

OK - I'll keep an eye on it.

Sam

Hi again,

Just to let u know that I haven't forgotten about this. I'm still trying to
figure out what this is doing. It is not copying the data I need at the
moment and I can't see what is copying.

What I can see what is happening/not happening I'll let you know.

Thanks
LiAD

 

[LiAD]

Hi,

I have tried the two codes you've given me.

The first one still works, with the same issues that I've seen before.

- it copies from the first and second sheets, not just the 3rd-33rd, (named
1-31)
- it copies just from row 12, not 12 and 13/14 if needed
- adjustable paste destinations depending on whether it has 1,2 or 3 inputs

Using the second code i get a list of empty cells (using the same input
files). I can't see if its copying anything or if its copying from a
different area from some reason.

I'll keep looking but if you have any ideas in the meantime please let me
know.

I have attached the exact code I'm using.

LiAD

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:\dddd\"

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

 

[Sam Wilson]

Hi, the problem is with this line:

If not xws.Index < 3

It should be:

If not ws.Index < 3

Sam

 

[LiAD]

Hi,

Thankyou again and again. The copy part is working fine now.

The last part (I hope) of this puzzle is to have some way of copying the
date that will be recorded in cell C3 of the register sheets to col. C of the
summary file.

Could I also ask for some advice to how its best to have the copy work please?
At the moment it copies every day of the year once per day – is someone
comes and changes data from two months ago the macro will write over it.
Alternatively if the macro only copied the data that was recently entered
then 1) it would be quicker and 2) save any overwriting. I guess to make
this work the macro would need to store the last date it copied, (the worst
case would be a four week gap between two consecutive for summer hols for
example 10 aug to 10 sep), or the user would have to enter which date it
would like to copy – maybe through a message box/pop up?

The second option would be more ‘user friendly’ I think. Is this possible
to code?

Thanks a million for your help
LiAD

 

[Sam Wilson]

Hi,

No problem re the help - if you mark the helpful answers as "Answer" it'll
help anyone else who searches ther forums for something similar.

To get the date in you need to add to these 3 lines:

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

the extra lines

xws.Range("c2").Offset(j, 0).Value = ws.Range("C3").Value
xws.Range("c2").Offset(j, 0).numberformat = ws.Range("m12").Offset(k,
0).numberformat

As for the second part of your query, virtually anything you can logically
explain can be coded, so yes, either proposition is possible. The first
(storing the last run date) would involve you having an extra, maybe hidden,
sheet and reading the date from cell A1, running the macro, writing the
current date to A1, and the second would be an input box from the user.

But, the way the sheets are named & teh code works doesn't actually make any
use of what the date is for the sheet that the data is being copied from.
What you'd have to do is add lines in to check whether C3 on the opened sheet
contained a date after the specified last-run, and then either copy or ignore
the data. It wouldn't be much faster than it is now.

I think you're netter off leaving the macro as it is now, and making it
clear to users that if they want to update the summary sheet they need to do
it by changing the relevant day sheet and waiting for the macro to bring in
their changes.

Sam