Help with VBA

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
955
WOudl someone be able to help me with some VBA, I have a sheet which has data in cells A1:D1000

I need the code to :
Sort the cells by column A then column B ( ascending ).
Look in cell E1 and the date that is in that cell.

Highlight all the data in cells A1:D1000 which in column A have a date which is inlcudin gthe date in cell E1 and for the next six days eg ( cell E1 = 15/6/08 so all data with dates in column A are fro 15/6/8 to 21/6/8 )

Then all that data copy this and paste this into a new sheet.

Any help appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
This can be done quite easily manually, and if required a macro can be recorded.

Hint : Is column F always blank (i.e. available as a helper column) ?
 
Upvote 0
Try:

Sub Macro1()

Dim MyDate As Date
MyDate = Range("E1").Value

Application.ScreenUpdating = False

Worksheets("Sheet1").Select
Columns("A:D").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending
Range("A1:A1000").Select
For Each cell In Selection
If cell.Value >= MyDate And cell.Value < MyDate + 5 Then
cell.EntireRow.Copy
Worksheets("Sheet2").Activate
Range("A65536").Select
Selection.End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
End If
Next

Application.ScreenUpdating = True

End Sub

HTH Jay
 
Upvote 0
This works fine yet I cannot see how or where it copies the data required and pastes into a new sheet.
 
Upvote 0
I have found where it goes but is there any way to change it so when the macro is run it delets the old information out of sheet 2 first.
 
Upvote 0
Sorry, i should've checked it before posting. Try:

Sub Macro1()

Dim MyDate As Date
MyDate = Range("E1").Value

Application.ScreenUpdating = False

Worksheets("Sheet1").Select
Columns("A:D").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending
Range("A1:A1000").Select
For Each cell In Selection
If cell.Value >= MyDate And cell.Value < MyDate + 5 Then
cell.EntireRow.Cut
Worksheets("Sheet2").Activate
Range("A65536").Select
Selection.End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Next

Application.ScreenUpdating = True

End Sub

HTH Jay
 
Upvote 0
This works fine except it cuts the information out of sheet 1, need the information to remain in sheet1. But need the old information in sheet2 to be deleted and then replaced with the new info depending upon the date when macro run.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top