Posts by ekfasy

    Hello,


    I had a macro that would prompt the user to choose a file/workbook (x) to open up, and then the macro would take some data from workbook x and insert and paste the lines into workbook y. Instead of opening up workbook x, I decided to just put the needed tab from workbook x and manually add it as a tab in workbook y. I'm trying to modify my code so that it will still take the data need from the newly added tab in workbook y, and insert and paste those rows into another tab in workbook y. However, I keep getting an error saying "Object Doesn't Support this Property or Method." Any ideas on how to fix this would be greatly appreciated!


    Code
    Set rngFound = .Find(What:=wsVENDOR, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False)


    Above is the line is where the error appears when I step through the code.



    Below is my whole code:



    Thanks!

    Hello,


    I have an excel file that had a different tab for every month of the year, as well as a YTD tab. On each of those tabs is the names of our sales people and the details of the deals that they sold below each person's name. Every month I have to go grab all details for each person and compile them in the YTD. Sometimes things change in different months, so I have to go through EVERY monthly tab to copy the data for EACH sales person and aggregate it on the YTD tab. I'm trying to write a macro that will automate this process.


    I have attached a sample document.


    Thank you in advance!


    forum.ozgrid.com/index.php?attachment/69317/

    Below is some code that I have:





    I keep getting errors, and I'm not sure what's wrong. I inserted a data validation drop-down box in "ABC-Quote to Customer-Testing_OzGrid" file in cell J1.


    So what I'm trying to do is have the user select and open the file from the vendor that we need, then they need to go to the drop-down box in "ABC-Quote To Customer-Testing_OzGrid" file and select the vendor from cell J1.
    Depending on which vendor (case) the user selects, will then determine which data to copy and paste to where.


    I need this because each vendor (case) format is different, but I need them all to copy and paste into the "ABC-Quote To Customer-Testing_OzGrid" file so that in the end they are all in a consistent format on the "ABC-Quote..." file.


    I need to get this done ASAP, but every time I fix an error, another one pops up. :(forum.ozgrid.com/index.php?attachment/69300/forum.ozgrid.com/index.php?attachment/69301/I have attached the files!


    Thanks,
    Erinn


    Re: Excel VBA Copy & Insert Cells for a Variable Range


    Bruce,


    I just realized one thing... wb1 has the columns in a different order than wb2, so my first set of coding that you helped me with on another thread would copy each column from wb1 separately and then paste it into the correct column in wb2. Code Below:



    But the new code you just provided correctly copies the data from wb1 and inserts the appropriate amount of rows into wb2 when it pastes the data in, but it doesn't account for the fact that the columns are in a different order..


    For example in wb1 the columns are in this order:


    (column A) Product Code
    (column B) Product Description
    (column C) Quantity
    (column D) List(Unit)
    (column E) List Price


    In wb2, however, the columns are in the following order:
    (column B) Quantity
    (column C) Item Number <<same thing as product code>>
    (column D) Product Description
    (column E) Unit List Price
    (column F) Ext List Price


    So the columns from the different workbooks line up like this:
    [TABLE="width: 500"]

    [tr]


    [td]

    wb1

    [/td]


    [td]

    wb2

    [/td]


    [/tr]


    [tr]


    [td]

    column A

    [/td]


    [td]

    Column C

    [/td]


    [/tr]


    [tr]


    [td]

    Column B

    [/td]


    [td]

    Column D

    [/td]


    [/tr]


    [tr]


    [td]

    Column C

    [/td]


    [td]

    Column B

    [/td]


    [/tr]


    [tr]


    [td]

    Column D

    [/td]


    [td]

    Column E

    [/td]


    [/tr]


    [tr]


    [td]

    Column E

    [/td]


    [td]

    Column F

    [/td]


    [/tr]


    [/TABLE]



    Any ideas on how to mesh the two codes together so that it inserts the rows from the code you just gave me, but also keeps the columns lined up properly like in my previous code?

    Re: Excel VBA Copy &amp; Insert Cells for a Variable Range


    I went through and double checked everything, including extensions, spelling, and making sure the file is open. It looks really wonky though when I run it now. It inserts a bunch of lines and then only copy and pastes in one column of data, then inserts more lines...

    Re: Excel VBA Copy &amp; Insert Cells for a Variable Range


    Bruce,


    I tried the code you provided and then tried the code with the file extension to the workbooks mentioned in the macro, but with both there is an error saying "subscript out of range" and does nothing. Any ideas?


    Thanks!

    Hello,


    I currently have VBA coding that is copying a variable range from one excel spreadsheet and pasting it into another.


    Below is the code:


    Code
    [/COLOR]Sub MoveData()    Dim wb1 As Workbook, wb2 As Workbook    Dim lr As Long    Set wb1 = Workbooks("Copy of XYZ-ABCQuote-Testing") ' Change the name to match your workbook.    Set wb2 = Workbooks("ABC-Quote To Customer-Testing") ' Change the name to match your workbook.    With wb1.Worksheets("Quote") 'Change the name to match your worksheet.        lr = .Cells(.Rows.Count, "A").End(xlUp).Row        .Range(.Cells(21, 1), .Cells(lr, 1)).Copy _        Destination:=wb2.Worksheets("ABC Quote").Range("C24") 'Change the name to match your worksheet.               lr = .Cells(.Rows.Count, "B").End(xlUp).Row        .Range(.Cells(21, 2), .Cells(lr, 2)).Copy _        Destination:=wb2.Worksheets("ABC Quote").Range("D24") 'Change the name to match your worksheet.               lr = .Cells(.Rows.Count, "C").End(xlUp).Row        .Range(.Cells(21, 3), .Cells(lr, 3)).Copy _        Destination:=wb2.Worksheets("ABC Quote").Range("B24") 'Change the name to match your worksheet.               lr = .Cells(.Rows.Count, "D").End(xlUp).Row        .Range(.Cells(21, 4), .Cells(lr, 4)).Copy _        Destination:=wb2.Worksheets("ABC Quote").Range("E24") 'Change the name to match your worksheet.               lr = .Cells(.Rows.Count, "E").End(xlUp).Row        .Range(.Cells(21, 5), .Cells(lr, 5)).Copy _        Destination:=wb2.Worksheets("ABC Quote").Range("F24") 'Change the name to match your worksheet.           End With    End Sub[COLOR=#333333]



    [/COLOR]Does anyone know if it is possible to not just copy and paste these cells from wbk1 into wbk2, but instead to copy the cells from wbk1 and insert rows for those cells into wbk2. The number of rows will vary each time I run this macro, so it's important that the range it's being pasted into can be variable in the number of rows as well.


    Any advice would be much appreciated!!!


    Thanks :)



    Also posted on:
    http://www.mrexcel.com/forum/e…y-insert-cells-range.html


    and


    http://www.excelforum.com/show…565&p=4394099#post4394099

    Re: VBA Code to take data from a range that varies in size


    Ok, so I fixed that and it finally worked!


    It copied all of wbk1 into wbk2 though.. I tried messing with some of the numbers in the vba coding and I have no idea what I'm doing so it messes up when I try.


    I'm assuming it has to do with the following coding:


    Code
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range(.Cells(2, 1), .Cells(lr, 4)).Copy _


    I just don't know which numbers to change.


    I promise I'll be all done asking questions after this! :party:

    Re: VBA Code to take data from a range that varies in size


    forum.ozgrid.com/index.php?attachment/69211/forum.ozgrid.com/index.php?attachment/69212/


    This might help.. I used the exact files that I need for this macro to work (but took out the sensitive information).


    "Copy of XYZ-ABCQuote-Testing" is wbk1 --> Quote to us from our vendors
    "ABC-Quote to Customer- Testing" is wbk2 --> Quote we make and send to our customers


    I need range A21:E41 in wbk1 to copy into range B24:F44. These ranges will change every time that we get a new quote, which is captured in the macro, I believe.

    Re: VBA Code to take data from a range that varies in size


    Thanks Bruce!


    So I set up the call function, and it seems to work.




    Butttt I'm getting a "run-time error '9': subscript out of range." So I was stepping through the code, and it's highlighting this line:


    Code
    Set wb1 = Workbooks("Book1") ' Change the name to match your workbook.



    Any ideas on what's wrong?



    Thanks!

    Re: VBA Code to take data from a range that varies in size


    skywriter,


    you are awesome! I'm a complete novice to vba programming, so I'm so sorry if this is an obvious answer, but how do I get your code and my code to mesh together? Can you have more than one sub in a macro?



    and:



    I apologize for all of the questions, I'm trying to learn all of this as I go..

    Re: VBA Code to take data from a range that varies in size


    I currently have the following in my vba coding:




    Some background for this:
    I am automating a quote conversion process. Vendors send us quotes, and then we take data from their quotes and put it in our quote format to send to our customers (we are a reseller company). So workbook1 is going to be whichever vendor quote we are using at the time to convert to our own quote format. Our quote format will always be workbook2.


    My idea was to have workbook2 (our quote format that we send to our customers) to contain all of the vba coding within it. When the user (one of our sales people) clicks a button or runs the macro within workbook2, then they will be asked to browse on their computer for the vendor quote that they want to use, then the macro will open up that particular vendor file (workbook1). therefore, workbook1 will never have the same file name because the user will be usinga different version of a vendor quote each time they run the macro.


    From there, the macro would take data (like the data I provided in my first post) and bring that data over to workbook2 (column order would change etc.). Since workbook1 will vary in size in terms of how many products the vendor is quoting us for (the number of rows in the data I provided in my first post will change depending on how many products we are looking to quote), then the vba coding needs to know what range to take from workbook1 when the range varies each time.


    I'm sorry if this made things more confusing, I'm just trying to provide some more detail in case it helps.



    To go back to your last response:

    Quote

    If workbook you want to be wb1 is not open, then whatever workbook you are opening can be set to wb1 in the workbook opening code.


    I need workbook1 to be set to whatever file the user decides to open using the vba coding in the beginning of this reply. So if there is a way to fit the coding you provided into the coding above that I currently have, then that would be amazing!

    Re: VBA Code to take data from a range that varies in size


    sorry skywriter, but I have one more question relating to this. What if the name of workbook1 is always different (i.e. workbook1 is always going to be a quote to us from one of our vendors, so every time a user gets a quote from one of our vendors, the workbook name is always going to be different)? Would this coding still work if we added an inputbox or msgbox so that it would ask the user to browse for the workbook that they want to use as workbook1?