Fetching data from One Sheet to another in the same work book

[belguy]

Hello All, I am new to working on Excel Macros or VBA.All these days I was a Java developer and recently promoted to be a Manager.

I have a questions as I have started working on the Excel more often.

I have a Workbook, with sheet1 having more than 10k rows.
The first row has headers with Values as:

Contractor ID , EMP ID, Group# ,First name,Last Name,hours,Rate .

Each Group# has about 5-10 employees.I am responsible for around 15 Groups.
I want to copy to another sheet, entire row,which has the Group# that I am responsible.

The format of sheet1 gets changed very often but has Group# always as a coloumn,Sometimes Coloumn C,Sometimes Coloumn K etc.

How Can I do this? If my question is confusing, then I can give more information. Please let me know the solution.

 

[JLGWhiz]

Welcome to the Forum/

You could probably just use4 the Filter tool on the Ribbon (xl2007 +) or the menu bar (xl2003 -).
Select the entire database, then click on Filter. In the column with the Group#, click the down arrow, then chech only the groups that are yours, make sure the rest are unchecked. Click Ok. Then select the visible range of cells and copy it to a new worksheet.

 

[L. Howard]

With the changing Group# column, there are other managers Group#'s interspersed in the ever changing column. Is that true?
Where one must find and copy the entire row of your Group# within that column?

...but has Group# always as a coloumn, Sometimes Coloumn C, Sometimes Coloumn K etc.

Regards,
Howard

 

[belguy]

Hello,,
Thanks for the response..The filtering options are done.I wanted to do the other way.Because, sheet 1's data is changing completely every day and only column common is Group#. The feed data for sheet1 is coming from many reports (Mon a different report, Tuesday from another report).I copy data from that report and paste the entire thing in Sheet 1.So My boss doesn't want to add Filter all the time and then select the Group#'s.
My Idea was to put all the Group# in a column A in one more sheet(Call it Sheet 2),then look those in the Sheet1 and display the entire matching row in sheet 2 or another new sheet. In this way whenever a new group is added I can just put the value of that in the end of Sheet 2.

I hope its clear now

 

[L. Howard]

Try this in the sheet vb module.

It assumes you Group# is a plain number such as 123456, 881144 etc. (which is a Long) So if the Group# is something like AA-123, 66-99-66 or such then Dim MyGpNum As Long will need to be changed to String or Variant.

In your workbook change the red book name to the name of you workbook, whatever it is.

So, you will run the code and get an input box asking for your Group#, enter it tap OK.
The next input box asks to select a column range, which you can do with the mouse.
Once selected tap OK.

Results go to Sheet 2 column A end(xlup). Each copy to sheet 2 will be below the previous.

Questions, post back.

Regards,
Howard

Option Explicit

Sub CopyManagerRangesSheet1_Sheet2()

  Dim rngSource As Range, sPrompt$
  Dim MyGpNum As Long
  Dim c As Range
  
  Workbooks("Book1 Manager By column and Row").Sheets("Sheet1").Activate
  
  MyGpNum = Application.InputBox("Enter your Group Number.", _
        "RANGE COLLECTOR", , , , , , 1)
  
  Const sTitle$ = "Copy Sheet to Sheet"
  sPrompt = "Select the Column Range to Evaluate"
  
  Set rngSource = Application.InputBox(Prompt:=sPrompt, _
                  Title:=sTitle, Type:=8)
  If rngSource Is Nothing Then Exit Sub
    
   For Each c In rngSource
     If c.Value = MyGpNum Then
        c.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
     End If
   Next
 End Sub