Help please

allex011 · #1 12-15-2017, 01:25 PM

Could some one help me,

I have two sheets, first have values (transfer finished, company, invoice No, values in usd, eur). In second I need some formula to look is the transfer finished for some company, and if it is find all values for that company (invoice No, values in usd, eur) and fill all row (like in screenshot).
Thank in andvanced

ProudLiberal · #2 12-15-2017, 02:36 PM

Will using filters accomplish what you want?

allex011 · #3 12-15-2017, 03:44 PM

Thank you for your quick replay, but I need to filter data with some formula from first into second sheet.

allex011 · #4 12-15-2017, 04:05 PM

I could put in third sheet list of all company and "yes" and "no" to help build formula in second sheet.

Logit · #5 12-15-2017, 04:39 PM

Here is a macro method. Paste this macro into a Routine Module. Run from Command Button on Sheet 1 :

Code:

Option Explicit

Sub CopyYes()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    
    Dim myString As String

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")
    
    myString = Application.InputBox("Enter A Search Term")
    
    j = 2     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("B1:B1000")   ' Do 1000 rows
        If c = myString Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

allex011 · #6 12-16-2017, 02:19 PM

Thank you Logit, you save me

Logit · #7 12-16-2017, 03:00 PM

.
You are welcome.

Glad to help.

ArviLaanemets · #8 12-17-2017, 11:54 PM

Somehow my post having attached excel file with a possible solution from Friday disappeared. Probably I'm late now, but here it is again (differently from Friday's version, I changed the file format to xlsm and added an Open event to update the query connection string and query text string whenever the file is moved or renamed).

ArviLaanemets · #9 12-18-2017, 12:14 AM

Sorry! I forgot to attach the file!

allex011 · #10 12-18-2017, 12:41 AM

Thank you your reply, but I have problem with excel document (I receive message " is not a valid path.make sure that the path name is correctly written and that you are connected on the server on which files are located.

Pecoflyer · #11 12-18-2017, 01:14 AM

@Allex

Hello
as you might suspect, the title of a thread is very important :
- for you because the more descriptive it is, the higher the chance you have to get help from one or many members. Most of us usually only check thread titles to see if the subject is part of our knowledge. You will admit that " Please help" does not really " help".
- for others because when the thread is solved, they can profit from the answer to solve their own problems. The thread title can also be searched on the Net. You will admit that " please help" will not be searched for very often...

So, I would suggest, so that all can benefit from it that you change your thread title to something more descriptive.
To do this edit your first post, click " go advanced" and there you can change your title accordingly.
Thanks

ArviLaanemets · #12 12-18-2017, 01:54 AM

Quote:

Originally Posted by allex011

I receive message " is not a valid path.make sure that the path name is correctly written and that you are connected on the server on which files are located.

It looks like ODBC query is not edited (It tries to connect with Excel file in folder of my Excel profile). The Open event must take care of it, but obviously it is disabled currently.

You can open the file anyway when you get ODBC error - simply click Cancel for error messages.

Then you must save the file into folder accessible from your computer (to hard disk, or to network resource - ODBC query reads info from the last save of file, not from instance you are working with). After that close the file and open again - When no error massages anymore, then all is OK.

When you get same error anyway, then check security settings (for macros and external connections).

When you anyway can't get the Open event or query working properly, then the last option is to edit connection manually (from menu: Data>Connections select connection, activate Properties, on tab Definition edit file path in Connection string and Command text.)

As the query reads data from save, when you enter some new transfers and want to see them in report, you have to save the file before refreshing the report.

The report is refreshed, whenever:
1. you open the file;
2. you click on Refresh All in Connections section of data menu;
3. you click on Refresh button in Workbook Connections wizard with connection selected;
4. you right-click on any cell of query's datarange on report sheet, and select Refresh from drop-down menu;
5. You select another company or Finished status on report sheet.