none
post a value in a specific cell to main work book by a button. RRS feed

  • Question

  • i want to post a value in a cpecific cell from different work books to a single main work book that works as monthly database.

    to be more specific, i make invoices in excel work book and they are all saved as different files

    e.g bill 1, bill 2, bill 3 and so on.

    now i want to create a button in the "bill file" that , when i press , post the amount of invoice(which is in cell f 40)  in the cell d1 in database work book (a seprate work book), but every bill (when i click on the button) should be post the amount of invoice in the next available cell in the same column.

    for example :

    bill 1 (click the button) paste the value in f 40 in work book named bill 1 to the cell d 1 in the database work book.

    bill 2 (click the button) paste the value in f 40 in work book named bill 2 to the cell d 2 in the database work book.

    bill 3 (click the button) paste the value in f 40 in work book named bill 3 to the cell d 3 in the database work book.

    bill 4 (click the button) paste the value in f 40 in work book named bill 4 to the cell d 4 in the database work book.

    bill 5 (click the button) paste the value in f 40 in work book named bill 5 to the cell d 5 in the database work book.

    bill 6 (click the button) paste the value in f 40 in work book named bill 6 to the cell d 6 in the database work book.

    and so on......

    i need it to be done ASAP , kindly help me solving this problem.

    thanks.

    • Edited by Mirza Saqib Wednesday, January 1, 2014 10:25 AM
    Wednesday, January 1, 2014 10:04 AM

Answers

  • I'll assume that the database workbook is in the same folder as the bill workbooks. You can assign the following macro to the command button:

    Sub CopyValue()
        Dim strPath As String
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Dim cel As Range
        strPath = ThisWorkbook.Path
        If Right(strPath, 1) <> "\" Then
            strPath = strPath & "\"
        End If
        ' Substitute the correct file name
        Set wbk = Workbooks.Open(strPath & "Database.xlsx")
        Set wsh = wbk.Worksheets(1) ' modify if necessary
        Set cel = wsh.Range("D" & wsh.Rows.Count).End(xlUp).Offset(1)
        cel.Value = ThisWorkbook.Worksheets(1).Range("F40").Value
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mirza Saqib Wednesday, January 8, 2014 8:56 AM
    Wednesday, January 1, 2014 12:02 PM
  • Try this version:

    Sub CopyValue()
        Dim strPath As String
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Dim cel As Range
        strPath = ThisWorkbook.Path
        If Right(strPath, 1) <> "\" Then
            strPath = strPath & "\"
        End If
        ' Substitute the correct file name
        Set wbk = Workbooks.Open(strPath & "Database.xlsx")
        Set wsh = wbk.Worksheets(1) ' modify if necessary
        Set cel = wsh.Range("D" & wsh.Rows.Count).End(xlUp).Offset(1)
        cel.Value = ThisWorkbook.Worksheets(1).Range("F40").Value
        wsh.Hyperlinks.Add Anchor:=cel, _
            Address:=ThisWorkbook.FullName, _
            SubAddress:="'" & wsh.Name & "'!" & cel.Address, _
            TextToDisplay:=wsh.Name
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mirza Saqib Sunday, January 12, 2014 8:39 AM
    Wednesday, January 8, 2014 8:49 PM

All replies

  • I'll assume that the database workbook is in the same folder as the bill workbooks. You can assign the following macro to the command button:

    Sub CopyValue()
        Dim strPath As String
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Dim cel As Range
        strPath = ThisWorkbook.Path
        If Right(strPath, 1) <> "\" Then
            strPath = strPath & "\"
        End If
        ' Substitute the correct file name
        Set wbk = Workbooks.Open(strPath & "Database.xlsx")
        Set wsh = wbk.Worksheets(1) ' modify if necessary
        Set cel = wsh.Range("D" & wsh.Rows.Count).End(xlUp).Offset(1)
        cel.Value = ThisWorkbook.Worksheets(1).Range("F40").Value
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mirza Saqib Wednesday, January 8, 2014 8:56 AM
    Wednesday, January 1, 2014 12:02 PM
  • You can export dates using my code: Split table data for seperate sheets

    Key is selecting column with month (as I understood)


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Wednesday, January 1, 2014 12:06 PM
    Answerer
  • thanks mr. Hans.

    i was able to solve my problem.

    now there is one other thing i want to do in the same file and the same macro,

    that is to create a hyperlink of the file(bill 1) in which i have put "copy value" macro, in the database file.

    i have tried it myself but i'm stuck at the address property of hyperlink, cause the address is a variable.

    help me fix this.

    Wednesday, January 8, 2014 9:05 AM
  • Where exactly do you want to create the hyperlink?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 8, 2014 2:53 PM
  • read my previous question , keep it in mind i'll explain.

    macro u told me how to posts value from cell f40 of file "bill 1" to the cell a1of database file,

    and from cell f40 of file "bill 1" to the cell a1of database file,

    and from cell f40 of file "bill 1" to the cell a1of database file, and so on...

    i want that value posted from cell f 40 of file "bill 1" to cell A1 of the database file should also become a huperlink.

    that is, a hyperlink to be created in cell a1 of the database file , which opens the file "bill 1"

    so that if i'm seeing the database file , i would be able to open the relevat file for a value in cill A1 of the database file.

    and i want to create the hyperlink through the same macro button, that posts the value from cell f 40 of file "bill 1" to the cell a1 of the database file.

    hope you find me the soloution.

    Wednesday, January 8, 2014 6:26 PM
  • Try this version:

    Sub CopyValue()
        Dim strPath As String
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Dim cel As Range
        strPath = ThisWorkbook.Path
        If Right(strPath, 1) <> "\" Then
            strPath = strPath & "\"
        End If
        ' Substitute the correct file name
        Set wbk = Workbooks.Open(strPath & "Database.xlsx")
        Set wsh = wbk.Worksheets(1) ' modify if necessary
        Set cel = wsh.Range("D" & wsh.Rows.Count).End(xlUp).Offset(1)
        cel.Value = ThisWorkbook.Worksheets(1).Range("F40").Value
        wsh.Hyperlinks.Add Anchor:=cel, _
            Address:=ThisWorkbook.FullName, _
            SubAddress:="'" & wsh.Name & "'!" & cel.Address, _
            TextToDisplay:=wsh.Name
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mirza Saqib Sunday, January 12, 2014 8:39 AM
    Wednesday, January 8, 2014 8:49 PM
  • thanks mr. hans.

    now i want to do one more thing. keep in mind the previous questions for better understanding.

    in the database file, i have posted 4 values from different cells in a bill file.

    that include date, bill #, amount and sales point.

    now i want  to filter this data according to sale points, a want that filtered sale poit's values to be copied in a different range of cells in the database file.

    i have tried advanced filter options, but i'm stuck at the "criteria range".

    suppose there are 4 sale points in total(sale point 1)(sale point 2)(sale point 3)(sale point 4), i want to filter and copy all the values entered based on the sale point, in a different range of cells in the database file.

    help me out.

    Sunday, January 12, 2014 8:51 AM
  • Here is a screenshot that may help:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 12, 2014 10:30 AM
  • i got this. thanks.

    but i want that when i apply filter for once , it automaticall filters and copy

     all data of sale point 1 to the range e.g a100:d110

    and all data of sale point 2 to the range a200:d210

    and all data of sale point 3 to the range a300:d310

    and all data of sale point 4 to the range a400:d410.

    how to do this ??

    Sunday, January 12, 2014 11:16 AM
  • Enter the text Sales Point in cell F1.

    Run the following macro:

    Sub FilterAll()
        Dim varSalesPoint As Variant
        Dim i As Long
        Dim rng As Range
        Application.ScreenUpdating = False
        For Each varSalesPoint In Array("Sales Point 1", "Sales Point 2", _
                "Sales Point 3", "Sales Point 4")
            Range("F2").Value = varSalesPoint
            i = i + 1
            Set rng = Range("A" & 100 * i).Resize(1, 4)
            Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                CriteriaRange:=Range("F1:F2"), CopyToRange:=rng
            Range("A1:D1").Copy
            rng.PasteSpecial xlPasteFormats
        Next varSalesPoint
        Range("F2").ClearContents
        Application.ScreenUpdating = True
    End Sub

    You can assign this macro to a Forms command button if you like.

    See http://sdrv.ms/1ez0rZL for a sample workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 12, 2014 11:58 AM
  • its not like this , may be i could not explain good. sorry for that.

    sale poits are in text. like city names.

    Consider these sale points as follows,

    london

    new york

    california

    paris

    and now tell me how to filter and copy data.

    sorry for making u repeat things.

    Sunday, January 12, 2014 2:20 PM
  • You can change the values in the array in

        For Each varSalesPoint In Array("Sales Point 1", "Sales Point 2", _
                "Sales Point 3", "Sales Point 4")

    For example:

        For Each varSalesPoint In Array("London", "New York", _
                "California", "Paris")

    If you have more cities, you can expand the array.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 12, 2014 2:36 PM
  • hi mr hans.

    here is a string, I'm working on it but don't know the right parameter to use. kindly help me out.

                "If ThisWorkbook.Worksheets(1).Range("B4").Value(1 ' 99) Then
                 Set wbk = Workbooks.Open("H:\abc.xlsx")"

    in this string I want to open a work book based on the value of cell "B4".

    e.g. if the value in cell B4 is greater than 99 and lower than 200 (form 100 to 199 in other words) then open the work book abc.xlsx.

    I have underlined the portion in the string that is bothering me, kindly help me fix it.

    thanks.

    Tuesday, January 28, 2014 8:36 AM
  • Use

        If ThisWorkbook.Worksheets(1).Range("B4") >= 100 And _
            ThisWorkbook.Worksheets(1).Range("B4") <= 199 Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 28, 2014 3:40 PM
  • hi Mr. Hans.

    in excel. I have created a hyperlink in worksheet 1 cell A5, to the cell E15 in the worksheet 2.

    while the values in the column E of worksheet 2 are being posted periodically.

    I want that hyperlink takes me to the last cell in the column E containing any value, which is changing by the entries being posted periodically.

    I have tried giving it a range, but it gives a message "INVALID CELL REFERANCE".

    help me .

    Thursday, February 20, 2014 4:29 PM
  • You can use the following formula:

    =HYPERLINK(CELL("address",INDEX(Sheet2!E:E,MAX(MATCH(1E+307,Sheet2!E:E),MATCH(REPT("z",255),Sheet2!E:E)))))

    or

    =HYPERLINK(CELL("address",INDEX(Sheet2!E:E,MAX(MATCH(1E+307,Sheet2!E:E),MATCH(REPT("z",255),Sheet2!E:E)))),"Last cell in Sheet2, Column E")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, February 20, 2014 9:57 PM
  • hi Mr. Hans,

    thanks for your support previously. things were going good, until now. I recently encountered a problem regarding hyperlink string. the string is ok , it works correctly, but when I move or rename the "Database file" hyperlink does not open the file. it gives a message "can not open the file".

    kindly help me on this issue in the way that whether or not I move or rename the "database file" the hyperlinks should open the file.

    waiting for your solution soon. thanks

     

    Tuesday, April 15, 2014 10:07 AM
  • We've been discussing both VBA code and formulas in this thread. What exactly are you referring to? Please try to give a specific answer.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 15, 2014 2:32 PM
  • ok, previously u gave me the following string to auto-create a hyperlink.

    Sub CopyValue()
       
    Dim strPath As String
       
    Dim wbk As Workbook
       
    Dim wsh As Worksheet Dim cel As Range
        strPath
    = ThisWorkbook.Path
       
    If Right(strPath, 1) <> "\" Then
            strPath
    = strPath & "\"
       
    End If
       
    ' Substitute the correct file name
       
    Set wbk = Workbooks.Open(strPath & "Database.xlsx")
       
    Set wsh = wbk.Worksheets(1) ' modify if necessary
       
    Set cel = wsh.Range("D" & wsh.Rows.Count).End(xlUp).Offset(1)
        cel
    .Value = ThisWorkbook.Worksheets(1).Range("F40").Value
        wsh
    .Hyperlinks.Add Anchor:=cel, _
            Address
    :=ThisWorkbook.FullName, _
            SubAddress
    :="'" & wsh.Name & "'!" & cel.Address, _
            TextToDisplay
    :=wsh.Name
    End Sub

    It works well , but I have recently encountered a problem. when I rename or move the "Database.xlsx" , the hyper link does not work, it gives the message "can not open the specific file".

    I need help on this problem.

    I want that the script should works as before but hyperlink should also work , when I move or rename the "Database.xlsx" file.

    waiting for your reply soon.

    Wednesday, April 16, 2014 1:21 PM
  • That is not possible. Excel cannot detect that you rename or move a file. You'll have to remove the hyperlinks and create them again when you do so.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 16, 2014 2:48 PM