Cycling through collection

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

Vacuum Sealed

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 (e-mail address removed)
 
Vacuum Sealed said:
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
Click to expand...



For setting up your loop,
Set wks = ("Jan", "Feb", "Mar"....."Dec")

For each Wks in Wkb
Click to expand...

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.
 
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
 
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,Dec"
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 / 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.
Click to expand...

So then, what you're saying is the solution only needs to process the
ActiveSheet?
 
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
 
Back
Top