Hi all

Was hoping someone could help me with the correct layout for cycling
through a collection of sheets which are exactly the same, all 12.

They represent one sheet for each month in the year and are structured
in the following:

A5:J1500

The range from each sheet needs to be copied to the summary sheet.

Kinda something like:

Dim Wkb as Workbook
Dim Wks as Worksheet
Dim Rng as Range
Dim TS as Worksheet

Set rng = ("A5:J1500")
Set TS = Sheets("Summary")
Set wks = ("Jan", "Feb", "Mar"....."Dec")

For each Wks in Wkb
Rng.copy(<"", SpecialCells(Rows.xlUp), Not xlFormulas

TS.Activate

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

Selection.PasteSpecial Paste:=xlPasteValues,Operation:=xlNone,SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


'Do my other stuff

TIA
Mick.

It appears that you are making this harder than it needs to be.
Send your file with a complete explanation and before/after examples
to

On Oct 1, 7:45*am, Vacuum Sealed wrote:
Hi all

Was hoping someone could help me with the correct layout for cycling
through a collection of sheets which are exactly the same, all 12.

They represent one sheet for each month in the year and are structured
in the following:

A5:J1500

The range from each sheet needs to be copied to the summary sheet.

Kinda something like:

Dim Wkb as Workbook
Dim Wks as Worksheet
Dim Rng as Range
Dim TS as Worksheet

Set rng = ("A5:J1500")
Set TS = Sheets("Summary")
Set wks = ("Jan", "Feb", "Mar"....."Dec")

For each Wks in Wkb
* * * * Rng.copy(<"", SpecialCells(Rows.xlUp), Not xlFormulas

TS.Activate

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
* * * * * * * * * * * *xlWhole, , xlNext).Select

Selection.PasteSpecial *Paste:=xlPasteValues,Operation:=xlNone,SkipBlanks _
* * * * :=False, Transpose:=False
* * *Application.CutCopyMode = False

'Do my other stuff

TIA
Mick.

"Vacuum Sealed" wrote in message
ond.com...
Hi all

Was hoping someone could help me with the correct layout for cycling
through a collection of sheets which are exactly the same, all 12.

They represent one sheet for each month in the year and are structured
in the following:

A5:J1500

The range from each sheet needs to be copied to the summary sheet.

Kinda something like:

Dim Wkb as Workbook
Dim Wks as Worksheet
Dim Rng as Range
Dim TS as Worksheet

Set rng = ("A5:J1500")
Set TS = Sheets("Summary")
Set wks = ("Jan", "Feb", "Mar"....."Dec")

For each Wks in Wkb
Rng.copy(<"", SpecialCells(Rows.xlUp), Not xlFormulas

TS.Activate

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

Selection.PasteSpecial
Paste:=xlPasteValues,Operation:=xlNone,SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


'Do my other stuff




For setting up your loop,

Set wks = ("Jan", "Feb", "Mar"....."Dec")

For each Wks in Wkb

Try:

for each wks in sheets(array("Jan", "Feb", "Mar"....."Dec"))

Your Rng.copy syntax could become:

Wks.Range("A5:J1500").copy

but I did not try to evaluate what you need to actually accomplish
copying non-blank rows from the range.


Search the archives for posts by Ron Rosenfield and Rick Rothstein, and
I think you'll find a lot of help there.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Sat, 01 Oct 2011 22:45:12 +1000, Vacuum Sealed wrote:

A5:J1500

The range from each sheet needs to be copied to the summary sheet.

Rng.copy(<"", SpecialCells(Rows.xlUp), Not xlFormulas


Can you expand on that?

Your "copy line" shows parameters of the Range.Copy method with which I am not familiar, leading to me believe you somehow want to limit what you copy, and not copy the range A5:J1500 as you initially write.
It'll be much easier to devise a solution with more information.

Hi everyone

Most, if not all of my code is a non-logical - pseudo code making it far
from suitable or correct.

Essentially, I need to copy all cells in the given range using Column
"A" as the decider of how much of that range is copied from each sheet.

I chose my OP range so as to anticipate an overflow of extra data should
it occur ( meaning the range should be only 12 - 1300 rows, But! ).

Column "A" will have values of which some of the trailing lower cells in
each sheet will/may have nested IF() formulae which does not need to be
included.

This is why in my non-logical - Pseudo Code I structured it as:

' Rng.copy(<"", SpecialCells(Rows.xlUp), Not xlFormulas)

everything beyond this point should be all good, just need a pointer to
cycle through each sheet.

Again..!

Thx heaps for the assist

Mick

On Sun, 02 Oct 2011 21:28:28 +1100, Vacuum Sealed wrote:

Hi everyone

Most, if not all of my code is a non-logical - pseudo code making it far
from suitable or correct.

Essentially, I need to copy all cells in the given range using Column
"A" as the decider of how much of that range is copied from each sheet.

I chose my OP range so as to anticipate an overflow of extra data should
it occur ( meaning the range should be only 12 - 1300 rows, But! ).

Column "A" will have values of which some of the trailing lower cells in
each sheet will/may have nested IF() formulae which does not need to be
included.

This is why in my non-logical - Pseudo Code I structured it as:

' Rng.copy(<"", SpecialCells(Rows.xlUp), Not xlFormulas)

everything beyond this point should be all good, just need a pointer to
cycle through each sheet.

Again..!

Thx heaps for the assist

Mick


OK. If I understand what you have written correctly, it seems there are two issues.

1. How to select just the 12 sheets you are interested in.
2. How to select just the data on those sheets in which you are interested.

Here are my thoughts.

The most general method to cycle through the 12 sheets depends on the contents of the WB. For example, if the sheets are the only thing in the wb, and the summary sheet is in another wb, you could just do something like:
For each ws in wb
... code ...
next ws

If there are other sheets than just the month sheets in the wb, then you have to either test each ws to ensure it is one you desire; or cycle through the names specifically. If you cycle through the names specifically, you'll have to decide what you want to do if the ws isn't there (ie. missing, misspelled, etc).

It is probably better, given your setup as I understand it, to call each worksheet specifically, so as to be able to test for its absence.

e.g. From what Cliff wrote:


for each wks in sheets(array("Jan", "Feb", "Mar"....."Dec"))
...
next wks

If the wks might be absent and is absent, this will result in an error, and you will need to decide how you want to handle that.

To select the range to copy, I suppose it depends in part if the range to copy will always be contiguous, with formulas and blanks at the bottom.

Assuming "worst case" where you might have blank cells and formula containing cells in column A interspersed with "valid cells", then one way to select the range to copy would be to hide the stuff you don't want to copy, and then select the visible cells to copy. It might run marginally faster to just find the first cell in column A that contains a formula:

Note the Application.ScreenUpdating lines. These will make the routine run much faster, but you may want to comment them out for debugging.
You will also need to explicitly define rg and rNoCopy as referring to the particular Month worksheet you are working on. Don't make the sheet Active; just set the ranges to refer to the particular sheet.

So, within the above loop, you might, in the code below, change the line

With Worksheets("Sheet1") to read
With Worksheets(wks.name)

and the Worksheets("Sheet2") references you might change to Worksheets("Summary")


=======================
Option Explicit
Sub foo()
Dim rg As Range
Dim rNoCopy As Range

With Worksheets("Sheet1")
Set rg = .Range("A5:a1500") 'just column A
Set rNoCopy = Application.Union _
(rg.SpecialCells(xlCellTypeFormulas), rg.SpecialCells(xlCellTypeBlanks))

Application.ScreenUpdating = False
rNoCopy.EntireRow.Hidden = True

Set rg = .Range("A5:J1500")

'clear destination worksheet
Worksheets("Sheet2").Cells.ClearContents

rg.SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Sheet2").Range("A1")
End With

'return worksheet to original state
rNoCopy.EntireRow.Hidden = False
Application.ScreenUpdating = True

End Sub
==============================


If you can guarantee that the Range to copy will always be a contiguous range starting at A5 and defined by having a constant in column A, you could set it up as below. Note the Resize method. We initially test just column A, then expand the selection out to Column J.

=======================
Sub RangeToCopy()
Dim rg As Range

Set rg = worksheets(wks.name).Range("a5:a1500").SpecialCell s(xlCellTypeConstants).Resize(columnsize:=10)

Debug.Print rg.Address

End Sub
=======================

Hope these ideas help

Hi

Sorry I have not responded as work commitments and other things been keeping
me busy.

Had a rethink on how I should approach this and what may be a better angle
rather than looping through all sheets.

Instead for this exercise, Sheet = "Oct"

This is a pseudo-nutcase on the fly kinda code that I hope you understand:

Sub Export_Oct()
Dim myRng as Range
Dim cData as Range
Dim c as Range

Set myRng = Columns("E:K")
Set cData = Columns("P:P")

IF Not cData = "" Then
Exit
End If
Else

For Each c in myrng

myRng.Copy
Sheets("Summary").Select
Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

Selection.PasteSpecial Paste:=xlPasteValues,Operation:=xlNone,SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Sheets("Oct").Select
cData = 1
Next c
End If

......................................

Appreciate the help

TIA
Mick

Mick,
I don't understand why you want to 'select' anything. That is so
inefficient!

Loop the sheets that you want. If they are always going to be the same
12 sheets then...

<more pseudo code
Const sSheetsToProcess As String = _
"Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,D ec"
Dim vSheetsToProcess As Variant, n As Integer
vSheetsToProcess = Split(sSheetsToProcess, ",")

With Sheets("Summary")
For n = LBound(vSheetsToProcess) To UBound(vSheetsToProcess)
.Cells(lNextRow, "A").Resize(lSrcRows, lSrcCols) = _
Sheets(vSheetsToProcess(n)).Range(sSrcData)
lNextRow = lNextRow + 1
Next 'n
End With 'Sheets("Summary")

The concept implied here is that the target range be sized to match the
source range, and assign the values in source to target in one shot.
(rngTarget.Value=rngSource.Value) No select, no copy, no paste, no turn
off copy mode!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Thu, 6 Oct 2011 22:57:43 +1100, "Vacuum Sealed" wrote:

Hi

Sorry I have not responded as work commitments and other things been keeping
me busy.

Had a rethink on how I should approach this and what may be a better angle
rather than looping through all sheets.

Instead for this exercise, Sheet = "Oct"

This is a pseudo-nutcase on the fly kinda code that I hope you understand:

Better if you describe in plain english what you want to do, then providing "pseudo-nutcase on the fly kinda code" and having us try to guess what you want.

The code I provided earlier, for any given sheet, should do what I thought you wanted to do on a particular sheet. Since that is not what you want, please describe, in words, what you want to have copied that the code snippet I provided is not doing.

Garry / Ron

Sorry if I have not explained myself clearly.

Basically, the end user of this WB did not like my initial idea of cycling
through each sheet, hence the change in tack.

Will try explaining each step.

Sheet = "Oct"

The Column Range that needs to be copied is myRng("E:K")

The rows that are actually copied are determined by cData which is ("P:P")

If cData = "" then that Row that intersects within myRng is copied to the
"Summary Sheet" using the Find 1st blank cell in Column ("A") Statement.

Once the range has been copied across to the "Summary Sheet", Go back to
Sheet("Oct") and place the value of "1" in row/Column where cData interects.

This will mean the next time this code is run it will step over any rows
within the (Row, myRng) WHERE cData < "" and only copy/paste rows where
cData = "".

Then loop back until there are is no more data to cop0ied.

Hope this better explains..

Thx heaps for your patience.
Mick.

Vacuum Sealed expressed precisely :
Garry / Ron

Sorry if I have not explained myself clearly.

Basically, the end user of this WB did not like my initial idea of cycling
through each sheet, hence the change in tack.

Will try explaining each step.

Sheet = "Oct"

The Column Range that needs to be copied is myRng("E:K")

The rows that are actually copied are determined by cData which is ("P:P")

If cData = "" then that Row that intersects within myRng is copied to the
"Summary Sheet" using the Find 1st blank cell in Column ("A") Statement.

Once the range has been copied across to the "Summary Sheet", Go back to
Sheet("Oct") and place the value of "1" in row/Column where cData interects.

This will mean the next time this code is run it will step over any rows
within the (Row, myRng) WHERE cData < "" and only copy/paste rows where
cData = "".

Then loop back until there are is no more data to cop0ied.

Hope this better explains..

Thx heaps for your patience.
Mick.

So then, what you're saying is the solution only needs to process the
ActiveSheet?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry

That wil be the quickest and simplest approach, unless you have something
else in mind, I'm happy for any solution to get moving on this so I can move
onto my next project which is not actually an Excel but an Acees
DB...yuck...

Thx again Garry

Appreciate your patience.

Mick