Hello.

In my work I collect info from 10 individual workbooks from 10 material
controllers.
Each one of the material controllers summarize their suppliers part
number and which parts that goes out of stock in the next three days.

Each afternoon one person summarize all of the ten workbooks and filter
out which production line that will be out of parts in these three
days.

I am trying to do a script that copys the info written on each one of
the material controllers workbook into one new workbook. Each one of
the material controllers workbook get copied onto a new worksheet in
the new workbook.

This is how far I have gotten.

Here's my question:

On one worksheet I will summarize all ten worksheets.
I want to have a macro that loops through the first worksheet and copys
the info if the info on the worksheet is updated on the current day.
This information is written on each of the material controllers own
workbook and copied.

If the worksheet is updated I want excel to copy a cell range (A1:L1)
and so on to the worksheet where the summarize is being made.

Because of the fact that there might be written cells already in the
summarize worksheet it can't be over written. It must copy the value to
clear cells.

If someone can provide a script for me to deal with I would be very
glad and it would make my life a little easier.

Best regards

/Anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=386117

I would think that you could refresh all the data--no matter if it's updated or
not.

If the summary didn't change, it'll still be copied into the new worksheet.

If that's ok, Ron de Bruin has tons of samples to combine worksheets from
different workbooks at:

http://www.rondebruin.nl/copy3.htm



a94andwi wrote:

Hello.

In my work I collect info from 10 individual workbooks from 10 material
controllers.
Each one of the material controllers summarize their suppliers part
number and which parts that goes out of stock in the next three days.

Each afternoon one person summarize all of the ten workbooks and filter
out which production line that will be out of parts in these three
days.

I am trying to do a script that copys the info written on each one of
the material controllers workbook into one new workbook. Each one of
the material controllers workbook get copied onto a new worksheet in
the new workbook.

This is how far I have gotten.

Here's my question:

On one worksheet I will summarize all ten worksheets.
I want to have a macro that loops through the first worksheet and copys
the info if the info on the worksheet is updated on the current day.
This information is written on each of the material controllers own
workbook and copied.

If the worksheet is updated I want excel to copy a cell range (A1:L1)
and so on to the worksheet where the summarize is being made.

Because of the fact that there might be written cells already in the
summarize worksheet it can't be over written. It must copy the value to
clear cells.

If someone can provide a script for me to deal with I would be very
glad and it would make my life a little easier.

Best regards

/Anders

--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=386117

--

Dave Peterson

Hello again.

Now I have finally found out some way to loop through my files but
there are still some major functions I can't understand how to solve.

Here's the loop:

Do While FNames < ""

'SourceRcount = SourceRange.Rows.Count

Set myBook = Workbooks.Open(FNames)

Set SourceRange = myBook.Worksheets(1).Range("A4:L20")

SourceRcount = SourceRange.Rows.Count

'Set destrange = basebook.Worksheets(1).Cells(counter, "O")
Set destrange = basebook.Worksheets(1).Range("A4")


'Skriv in arbetsbokens namn i kolumn H
basebook.Worksheets(1).Cells(counter, "M").Value = myBook.Name
If myBook.Worksheets(1).Range("L1") = Date Then

'SourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

With SourceRange
'Set destrange = basebook.Worksheets(1).Cells(counter,
"O"). _
Resize(.Rows.Count,
..Columns.Count)
Set destrange = basebook.Worksheets(1).Range("A4"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = SourceRange.Value
End If

myBook.Close False

counter = counter + SourceRcount
FNames = Dir()

Loop

The lines written in red above are where I have difficulties.

On every new worksheet I want only the rows which has some values
copied onto the destination worksheet.
On the destination worksheet I want the first copied values to be
copied to Cell A4 and the next values on the cells A5, A6 and so on.
When the loop continues to the next file and copies the values I want
it to continue to copy the values the next empty cell in the
destination worksheet.

Anyone who can help me?

/anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=386117

Since you're posting in plain text (thank you), none of the lines show up in
red.

Just add above the line you need an explanation for.

a94andwi wrote:

Hello again.

Now I have finally found out some way to loop through my files but
there are still some major functions I can't understand how to solve.

Here's the loop:

Do While FNames < ""

'SourceRcount = SourceRange.Rows.Count

Set myBook = Workbooks.Open(FNames)

Set SourceRange = myBook.Worksheets(1).Range("A4:L20")

SourceRcount = SourceRange.Rows.Count

'Set destrange = basebook.Worksheets(1).Cells(counter, "O")
Set destrange = basebook.Worksheets(1).Range("A4")

'Skriv in arbetsbokens namn i kolumn H
basebook.Worksheets(1).Cells(counter, "M").Value = myBook.Name
If myBook.Worksheets(1).Range("L1") = Date Then

'SourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

With SourceRange
'Set destrange = basebook.Worksheets(1).Cells(counter,
"O"). _
Resize(.Rows.Count,
Columns.Count)
Set destrange = basebook.Worksheets(1).Range("A4"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = SourceRange.Value
End If

myBook.Close False

counter = counter + SourceRcount
FNames = Dir()

Loop

The lines written in red above are where I have difficulties.

On every new worksheet I want only the rows which has some values
copied onto the destination worksheet.
On the destination worksheet I want the first copied values to be
copied to Cell A4 and the next values on the cells A5, A6 and so on.
When the loop continues to the next file and copies the values I want
it to continue to copy the values the next empty cell in the
destination worksheet.

Anyone who can help me?

/anders

--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=386117

--

Dave Peterson

I'm sorry but when I see my previous inpput I see some lines colored in
red. Why can't you not see this?

/Anders
Here's the loop:

Code:
--------------------


Do While FNames < ""

'SourceRcount = SourceRange.Rows.Count

Set myBook = Workbooks.Open(FNames)

Set SourceRange = myBook.Worksheets(1).Range("A4:L20")

SourceRcount = SourceRange.Rows.Count

'Set destrange = basebook.Worksheets(1).Cells(counter, "O")
Set destrange = basebook.Worksheets(1).Range("A4")


'Skriv in arbetsbokens namn i kolumn H
basebook.Worksheets(1).Cells(counter, "M").Value = myBook.Name
If myBook.Worksheets(1).Range("L1") = Date Then

'SourceRange.Copy destrange
' Instead of this line you can use the code below to copy only the values

With SourceRange
'Set destrange = basebook.Worksheets(1).Cells(counter, "O"). _
Resize(.Rows.Count, .Columns.Count)
Set destrange = basebook.Worksheets(1).Range("A4"). _
Resize(.Rows.Count, .Columns.Count) End With
destrange.Value = SourceRange.Value
End If

myBook.Close False

counter = counter + SourceRcount
FNames = Dir()

Loop
--------------------

The lines written in red above are where I have difficulties.

On every new worksheet I want only the rows which has some values
copied onto the destination worksheet.
On the destination worksheet I want the first copied values to be
copied to Cell A4 and the next values on the cells A5, A6 and so on.
When the loop continues to the next file and copies the values I want
it to continue to copy the values the next empty cell in the
destination worksheet.

Anyone who can help me?

/anders


--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=386117

You're accessing the posts through ExcelForums. Many connect to MS Newsservers
directly. (I got through the msnewsservers and don't see your colors.)

Why not just copy all the data you want--and then when you're done, just wipe
out the blank rows in one step.

on error resume next
destrange.parent.columns(1) _
.cells.specialcells(xlcelltypeblanks).entirerow.de lete
on error goto 0

a94andwi wrote:

I'm sorry but when I see my previous inpput I see some lines colored in
red. Why can't you not see this?

/Anders
Here's the loop:

Code:
--------------------


Do While FNames < ""

'SourceRcount = SourceRange.Rows.Count

Set myBook = Workbooks.Open(FNames)

Set SourceRange = myBook.Worksheets(1).Range("A4:L20")

SourceRcount = SourceRange.Rows.Count

'Set destrange = basebook.Worksheets(1).Cells(counter, "O")
Set destrange = basebook.Worksheets(1).Range("A4")


'Skriv in arbetsbokens namn i kolumn H
basebook.Worksheets(1).Cells(counter, "M").Value = myBook.Name
If myBook.Worksheets(1).Range("L1") = Date Then

'SourceRange.Copy destrange
' Instead of this line you can use the code below to copy only the values

With SourceRange
'Set destrange = basebook.Worksheets(1).Cells(counter, "O"). _
Resize(.Rows.Count, .Columns.Count)
Set destrange = basebook.Worksheets(1).Range("A4"). _
Resize(.Rows.Count, .Columns.Count) End With
destrange.Value = SourceRange.Value
End If

myBook.Close False

counter = counter + SourceRcount
FNames = Dir()

Loop
--------------------

The lines written in red above are where I have difficulties.

On every new worksheet I want only the rows which has some values
copied onto the destination worksheet.
On the destination worksheet I want the first copied values to be
copied to Cell A4 and the next values on the cells A5, A6 and so on.
When the loop continues to the next file and copies the values I want
it to continue to copy the values the next empty cell in the
destination worksheet.

Anyone who can help me?

/anders

--
a94andwi
------------------------------------------------------------------------
a94andwi's Profile: http://www.excelforum.com/member.php...o&userid=21077
View this thread: http://www.excelforum.com/showthread...hreadid=386117

--

Dave Peterson