Transfer data for one workbook to another automatically - vba

[Jose_L_Perez]

Need help... I have 2 workbooks One used internally for analyzing costing/pricing data (COSTING FILE TEMPLATE - NEW) and another workbook that is sent out to vendors for pricing/costing (VENDOR PRICING TEMPLATE). We have over 300 vendors that submit pricing that we have to transfer to our internal costing file to analyze.

What I need is a VBA code that would allow me pick a saved "VENDOR PRICING FILE" and transfer only the rows of data that have a cost to the internal "COSTING FILE" and sort the data by the item# on column A to keep like items togther.

Can not attach files at this time.

Please let me know if something like this is possible.

 

[Kamolga]

Need help... I have 2 workbooks One used internally for analyzing costing/pricing data (COSTING FILE TEMPLATE - NEW) and another workbook that is sent out to vendors for pricing/costing (VENDOR PRICING TEMPLATE). We have over 300 vendors that submit pricing that we have to transfer to our internal costing file to analyze.

What I need is a VBA code that would allow me pick a saved "VENDOR PRICING FILE" and transfer only the rows of data that have a cost to the internal "COSTING FILE" and sort the data by the item# on column A to keep like items togther.

Can not attach files at this time.

Please let me know if something like this is possible.

Hi,

It is indeed possible. My approach would be to open "vendor pricing file" when I open "costing file" (https://www.excel-easy.com/vba/events.html)

[LEFT][COLOR=#00a0c8][FONT='inherit']Sub Open_ExistingWorkbook()

    Workbooks.Open "C:\WorkbookName.xls"

    'OR

    'Workbooks.Open Filename:="C:\WorkbookName1.xls"

End Sub[/FONT][/COLOR]
[/LEFT]

To select the appropriate line, I would rather have a pivot table on a hidden sheet that has exactly the lines that I need, at the right format that I would copy-paste as values.
You can also copy paste each line based on a condition; so something like

Dim dCell as Range
For each dCell in Range("A2:A999")
If dcell.value="Ok" then
dcell.entirerow.copy
...
'CostingFile.activesheet.range("A1").end(xldown).offset(1,0).select
Activesheet.paste
End if
Next dcell

The issue with the method is it goes cell by cell to check the value, so can take quite a while.
Another option is .autofilter command, so you filter on a value
An easy way to properly copy-paste from one workbook to another and autofilter if you are not familiar is to record a macro and adapt it to the ranges that you need
​