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").SpecialCells(xlCellTypeConstants).Resize(columnsize:=10)
Debug.Print rg.Address
End Sub
=======================
Hope these ideas help
")