Need help - Macro to copy a specific range

[Dileep Chandran]

Hello everybody,

I have a macro to copy a range of cells (A2:AB100) from sheet1 and
paste it to another file as values.


Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:AB100")


I would like to insert one more code so as it should copy only todays
data;


If B2 = todays date then the range of cells to copy should be (A2:AB2)
If B3 = todays date then the range of cells to copy should be (A3:AB3)
If both B2 and B3 = todays date then the range of cells to copy should
be (A2:AB3)
If all the cells from B2 to B100 = todays date then the range of cells
to copy should be (A2:AB100)


Any help is greatly appreciated.


Thanks & Regards
Dileep Chandran

 

[Roger Govier]

Hi

Dim nrows as long
nrows=Application.Countif(Range("B2:B100"),Date)
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:AB" &
nrows)

 

[Dileep Chandran]

Thanks Roger, Instead of "Date", is it possible to give "Today()" ?

-Dileep

 

[Bob Phillips]

Date is VBA speak for TODAY()

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dileep Chandran]

Thanks Bob!!!

 

[Dileep Chandran]

Ok, Thats good. Before copying this specified range is it possible to
unhide all rows and columns in Sheet1?

-Dileep

 

[Roger Govier]

Hi Dileep

Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False

 

[Don Guillett]

or

Sub uh()
Rows.Hidden = False
Columns.Hidden = False
End Sub

 

[Dileep Chandran]

This is good. But I want to unhide all rows and columns in Sheet1 while
I am running the macro from Sheet2

Is it possible?

-Dileep

 

[Dileep Chandran]

I have inserted: Sheets("Sheet1").Select to the code. Its now working
fine.

Thank you all. Your timely help is really appreciated.

Regards
Dileep Chandran