How to copy one column values to another column based on conditionthat row2 contain todays date

[sanju]

Hi

I am struck up in my code and getting error. It is pretty simple one.

I have to copy values from column 32 row 3 onwards i.e. cell (x,32) to
column C row 3 onwards i.e. cell(x, C).
Column C is column whose row 2 contain today's date.

I hope I am clear. To simplify this I am taking an example:-

If cell (2,45) contain todays date then copy all cells(3 onwards, 32)
to cells(3onwards,45).
If cell(2,50) contain todays date then copy all cells(3onwards,32) to
cells(3onwards,45).
If cell(2,100) contain todays date then copy all cells(3onwards,32) to
cells(3onwards,100)
If cell(2,200) contain todays date then copy all cells(3onwards,32) to
cells(3onwards,200)

If anything is unclear, please let me know so that I can send you the
file.

Regards
San

 

[Jef Gorbach]

Hi

I am struck up in my code and getting error. It is pretty simple one.

I have to copy values from column 32 row 3 onwards i.e. cell (x,32) to
column C row 3 onwards i.e. cell(x, C).
Column C is column whose row 2 contain today's date.

I hope I am clear. To simplify this I am taking an example:-

If cell (2,45) contain todays date then copy all cells(3 onwards, 32)
to cells(3onwards,45).
If cell(2,50) contain todays date then copy all cells(3onwards,32) to
cells(3onwards,45).
If cell(2,100) contain todays date then copy all cells(3onwards,32) to
cells(3onwards,100)
If cell(2,200) contain todays date then copy all cells(3onwards,32) to
cells(3onwards,200)

If anything is unclear, please let me know so that I can send you the
file.

Regards
San

Here's a starting point for you.
You didn't say where the results are being copied to, so i sent them
to the second sheet tab.
It works when the Today variable is a fixed number (ie the date's
serial number, 40281 for 4-13-2010 for instance) however isn't finding
anything when i assign it to the system date via Now.


Sub FilterCopy()
Dim CopyRange As Range
Dim today
today = Now
Set CopyRange = Range("A1:k100")
CopyRange.AutoFilter Field:=3, Criteria1:=today
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A2")
Application.CutCopyMode = False
End Sub

 

[JLGWhiz]

I am not sure I understood everything, but give this a try and post back
with results.

Sub vert()
Dim lc As Long, sh As Worksheet, rng As Range
Dim lr As Long, cRng As Range
Set sh = ActiveSheet
lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = sh.Range("C2", sh.Cells(2, lc))
lr = sh.Cells(Rows.Count, 32).End(xlUp).Row
Set cRng = sh.Range(sh.Cells(3, 32), Cells(lr, 32))
For Each c In rng
If c = Date Then
cRng.Copy c.Offset(1, 0)
End If
Next
End Sub

 

[sanju]

I am not sure I understood everything, but give this a try and post back
with results.

Sub vert()
   Dim lc As Long, sh As Worksheet, rng As Range
   Dim lr As Long, cRng As Range
   Set sh = ActiveSheet
   lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column
   Set rng = sh.Range("C2", sh.Cells(2, lc))
   lr = sh.Cells(Rows.Count, 32).End(xlUp).Row
   Set cRng = sh.Range(sh.Cells(3, 32), Cells(lr, 32))
   For Each c In rng
      If c = Date Then
         cRng.Copyc.Offset(1, 0)
      End If
   Next
End Sub












- Show quoted text -

Hi it is fine . Working all right in active sheet. How can I make it
work for all the sheets if workbook.

Regards
sanjay

 

[sanju]

Thanks Whiz,
Please let me know there are any conditions for this. As in the same
worksheet, it was working for some column only. I could not figure out
why this is not working for other columns.

Regards
sanajy