Help with VBA

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
933
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
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) ?
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
933
As this is going to be done daily I would like a macro, column F is always blank.
 

TFCJamieFay

Active Member
Joined
Oct 3, 2007
Messages
480
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
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
933

ADVERTISEMENT

This works fine yet I cannot see how or where it copies the data required and pastes into a new sheet.
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
933
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.
 

TFCJamieFay

Active Member
Joined
Oct 3, 2007
Messages
480

ADVERTISEMENT

Change:
cell.EntireRow.Copy

For:
cell.EntireRow.Cut

That should work.

HTH Jay
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
933
That returns an error in :

Selection.End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
 

TFCJamieFay

Active Member
Joined
Oct 3, 2007
Messages
480
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
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
933
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,568
Members
414,389
Latest member
MarkElla

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
Top