PDA

View Full Version : display records by office



jimmymoon
04-28-2010, 02:00 PM
I am a newbie in Excel/VBA.
would you please help me to figure out this project.
First, get the data from source.
Second, Display the data based on office in the certain office place in Result sheet.
see attached excel file. would you help me
the following is so far what I done;

Sub ExtractDept()
Dim i As Integer
Dim intCount As Integer
Dim strDept As String
Dim startpoint As String
Dim Msg As String
Dim rngCell As Range
Dim strYesNo As String
Application.DisplayAlerts = False
'reference the range;NQ,SR,GP,WR,CC,DC
strDept = Range(OFFICE)
'startarea=NQ,SR,GP,WR,CC,DC
startpoint = Range(startarea)
'In a certain office area, paste records according to Office In Result sheet

Sheets(strDept & "Sheet").Range("a1").Select
ActiveSheet.Paste

Do While Range("start").Offset(i, 1) <> ""
If Range("start").Offset(i, 1) = strDept Then
' go to startpoint place ; startarea=strDept
Range(Range("start").Offset(i, 0), Range("start").Offset(i, 4)).Copy
intCount = intCount + 1
sht.Range("a1").Offset(intCount, 0).Select
sht.Paste
End If
i = i + 1
Loop

Columns.AutoFit
ActiveWindow.DisplayGridlines = False


End Sub

austenr
04-28-2010, 04:31 PM
Well right off the bat you have a named range (startarea) used in your macro that is not defined. What range would that be?

jimmymoon
04-29-2010, 06:36 AM
Well right off the bat you have a named range (startarea) used in your macro that is not defined. What range would that be?

Actually, it is physical areas for (NQ,SR,GP,WR,CC,DC) in resultsheet. I do not
know how to define in the macro.
would you mind see the attached file, there are the format box for NQ, SR,GP,WR,CC,DC
for example, whenever the records in sourcesheets are match with NQ , go to paste them to NQ format.
as well SR, go to paste them to SR format and so on..

thank you for your response. can you help me.

jimmymoon
04-30-2010, 06:48 AM
if it is hard, would you give me other ways to do or some idea.
thanks for your kindness

mdmackillop
04-30-2010, 05:08 PM
Check that all your named ranges are valid and repost your sample.