Help with Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to sort through a workbook using a macro but I am not very good at visual basic. I have a basic outline of the type of macro below. Could somebody help please?

The workbook contains a Summary page and a number of other worksheets. The worksheets contain information about customer deliveries. If the delivery is overdue, a cell will go red due to conditional formatting. The macro should search the workbook for overdue deliveries and copy the whole row into the summary sheet.

On open, go to Summary
Clear data from columns B to M
Go to sheet 1
If date in Column K < NOW(), copy entire row to Summary
Repeat for all cells in K that hold data
Go to sheet 2, etc etc etc
Repear Query for all sheets in workbook excluding Summary
1 blank row inserted between imported rows from different sheets

I know its asking a lot but all help is greatly appreciated. If you can help please e-mail me directly at (e-mail address removed)

Thanks,
Shane
 
Shane,

Sorry, if you post in the NG, I answer in the NG. Here is some code

Sub UpdateSheets()
Dim sh As Worksheet
Dim i As Long
Dim j As Long
Dim iTarget As Long
Dim cLastRow As Long

Worksheets("Summary").Columns("B:M").ClearContents
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> "Summary" Then
With Worksheets(i)
cLastRow = .Cells(Rows.Count, "K").End(xlUp).Row
For j = 1 To cLastRow
If .Cells(j, "K").Value <> "" And .Cells(j, "K").Value <
Date Then
iTarget = iTarget + 1
.Cells(j, "K").EntireRow.Copy
Destination:=Worksheets("Summary").Cells(iTarget, "A")
End If
Next j
End With
iTarget = iTarget + 1
End If
Next i
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Shane said:
I want to sort through a workbook using a macro but I am not very good at
Click to expand...
visual basic. I have a basic outline of the type of macro below. Could
somebody help please?
The workbook contains a Summary page and a number of other worksheets. The
Click to expand...
worksheets contain information about customer deliveries. If the delivery is
overdue, a cell will go red due to conditional formatting. The macro should
search the workbook for overdue deliveries and copy the whole row into the
summary sheet.
On open, go to Summary
Clear data from columns B to M
Go to sheet 1
If date in Column K < NOW(), copy entire row to Summary
Repeat for all cells in K that hold data
Go to sheet 2, etc etc etc
Repear Query for all sheets in workbook excluding Summary
1 blank row inserted between imported rows from different sheets

I know its asking a lot but all help is greatly appreciated. If you can
Click to expand...
help please e-mail me directly at (e-mail address removed)
 
Back
Top