Is there a somewhat generic code that will filter column A for say x, y, z, then in copy each column that has a header for any of the filtered rows.

Where, for example:

row x with MyHdr in columns 2,4,6 (each column a different length) would copy 2,4,6 to sheet mySheet

row y with MyHdr in columns 3,7,9 (each column a different length)would copy 3,7,9 to sheet mySheet next to row x data.

row z with MyHdr in columns 2,6 (each column a different length)would copy 2,6 to sheet mySheet next to row y data.

I can manually filter a sheet as I say above, and see what columns I need to copy but don't know how to code the copy of each column. I think I want to stay away from B:B.Copy, F:F.Copy and use .xlDown or .UsedRange.

And this would be for each sheet in myArray("..", "..", "..")
And MyHdr is in row 2 when it exists.

Howard

Hi Howard,

Am Tue, 7 Apr 2015 10:33:37 -0700 (PDT) schrieb L. Howard:

row y with MyHdr in columns 3,7,9 (each column a different length)would copy 3,7,9 to sheet mySheet next to row x data.

row z with MyHdr in columns 2,6 (each column a different length)would copy 2,6 to sheet mySheet next to row y data.

how to find row x data in mySheet?
Can you provide an example workbook with data in mySheet?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Tue, 7 Apr 2015 19:45:49 +0200 schrieb Claus Busch:

how to find row x data in mySheet?
Can you provide an example workbook with data in mySheet?

I have to quit for today.
Modify the ranges to copy and the destination to your table:

Sub Test()
Dim LRow As Long, i As Long
Dim varCrit As Variant
Dim myRng As Range, dest As Range

Application.ScreenUpdating = False

varCrit = Array("x", "y", "z")
With ActiveSheet
LRow = .UsedRange.Rows.Count
For i = 0 To UBound(varCrit)
.UsedRange.AutoFilter field:=1, Criteria1:=varCrit(i)
If varCrit(i) = "x" Then
'myrng is columns 2, 4, 6
Set myRng = Union(.Range(.Cells(2, 2), .Cells(LRow, 2)), _
.Range(.Cells(2, 4), .Cells(LRow, 4)), _
.Range(.Cells(2, 6), .Cells(LRow, 6)))
'Destination is column A:C
Set dest = Sheets("mySheet").Cells(Rows.Count,
1).End(xlUp)(2)
ElseIf varCrit(i) = "y" Then
'myrng is columns 3, 7, 9
Set myRng = Union(.Range(.Cells(2, 3), .Cells(LRow, 3)), _
.Range(.Cells(2, 7), .Cells(LRow, 7)), _
.Range(.Cells(2, 9), .Cells(LRow, 9)))
'Destination is column D:F
Set dest = Sheets("mySheet").Cells(Rows.Count,
4).End(xlUp)(2)
ElseIf varCrit(i) = "z" Then
'myrng is columns 2, 6
Set myRng = Union(.Range(.Cells(2, 2), .Cells(LRow, 2)), _
.Range(.Cells(2, 6), .Cells(LRow, 6)))
'Destination is column G:H
Set dest = Sheets("mySheet").Cells(Rows.Count,
7).End(xlUp)(2)
End If
myRng.Copy dest
Next
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

The code you provided is on the right track for as much info that I provided.

Since I am trying to modify a workbook that is nicely using code you have already supplied (slightly modified), I decided to use that workbook as the example.

https://www.dropbox.com/s/c5hp9d7g50...0Box.xlsm?dl=0

Master sheet is the sheet the code writes to, code is assigned to the gray star in U column.

What you see on Master sheet is exactly what is wanted with the code as it is.
Enter a start week and an end week (5 and 7) and you see the results.

What is needed is an "R" option, perhaps with an additional InputBox to evoke it, where the returns to Master would look like Master R Example sheet.

So the two options would either look like what is now on Master sheet or what is shown on Master R Example sheet. Nothing is written to the example sheet, it will be discarded when code is completed, and one or the other options will always go to Master.

Howard

Hi Howard,

Am Tue, 7 Apr 2015 21:55:41 -0700 (PDT) schrieb L. Howard:

https://www.dropbox.com/s/c5hp9d7g50...0Box.xlsm?dl=0

look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Week Reader with R"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

Impressive and masterful!

I am sure the end user will be equally impressed.

Thanks for the fine code and the time to do it.

Howard

Hi Howard,

Am Wed, 8 Apr 2015 03:24:45 -0700 (PDT) schrieb L. Howard:

Thanks for the fine code and the time to do it.

you are welcome. I am always glad to help.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

From the end user of the R_Only and other code you provided:

**********
Hi Howard
Well thanks so very much. The code is working perfectly and I am very impressed. It will take me a long time to decipher this but I actually enjoy this activity. Also, please pass along my gratitude to Claus for all of his help on this project. I will try to send him a separate email.
Thanks again.
Jim
*********

Howard