Copy and paste rows from one workbook to another skipping rows with a 0 (zero) in column C

[Riptake]

Hi everyone,

I'm a little new to VBA. Wanted to know if there is an easy to learn and customize VBA syntax that can serve my purpose. My task involves copying and pasting (as formatted values - color, decimal etc.) from one workbook to another based on the following criteria. For simplicity, lets assume the source workbook is called "Source" and info is in a tab called "Sheet 1". The output will be generated in a workbook called "Output", in a tab called "Report". The criterion are as follows:

1. If the displayed value in column C in the "Sheet 1" tab is a 0, the corresponding row will be skipped and the next row will be copied.
2. Only columns with matching column headers (in row 1 in both sheets) match, the corresponding data will be copied.


Any direction would be much appreciated.


Thanks

 

[farmerscott]

Riptake,

glad we are getting somewhere with this..

RE: your headers.

1. will the columns be (or can be) moved on the Source workbook? In other words, the columns you want copied across can change as a result of some manipulation to the worksheet (so we can't hard wire into the code to only copy Cols A,B,D &F)???
2. in the Output workbook, will you be putting in the Col headings before running the code??

FarmerScott

 

[Riptake]

Hi Farmerscott.

1. My plan is to keep the integrity of the source documents intact. To give to a little more insight, I will combine a data aggregation tool with this model, hence similar to the RDB merge add-in, there will be a variable set of source document workbooks, which will be aggregated and presented in the final output work book as well.

2. By association, the output work book will be in the same layout as the Source workbooks. However, I am not a big fan of hardcoding references into the code. Hence, my plan is to create an Index Library where the headers will be stored in the VBA. And after a Match function is run against the headers in the Output sheet, the data will be populated (similar to an Index-Match function).


Sorry if I haven't been to clear. It's a work in progress and hence I am trying to come up with additional functionalities as I experiment with the Macro.

Riptake,

glad we are getting somewhere with this..

RE: your headers.

1. will the columns be (or can be) moved on the Source workbook? In other words, the columns you want copied across can change as a result of some manipulation to the worksheet (so we can't hard wire into the code to only copy Cols A,B,D &F)???
2. in the Output workbook, will you be putting in the Col headings before running the code??

FarmerScott

 

[farmerscott]

Riptake,

My apologies but I am getting into parts of Excel I am not familiar with.

Can I suggest you start a new thread and hopefully one of the smart people on this forum will pick it up.

cheers

FarmerScott

 

[Riptake]

No worries Farmerscott. My issue has been resolved. I will start a new thread to speak about the data aggregation task (if and when I face problems).

Thank you very much for your help.


Best,

-Riptake

Riptake,

My apologies but I am getting into parts of Excel I am not familiar with.

Can I suggest you start a new thread and hopefully one of the smart people on this forum will pick it up.

cheers

FarmerScott

 

[TMililli]

Good Day,
I have a similar need as the above code, however I want it to populate a column with a value from its lookup.
For example

Two Files

Source: SoftwareNames.xlsx file Column Headers --

Columns
A | B | C | D
SoftwareName|Version|Edition|AssetID

Destination: Inventory.xlsx file - Column Headers

Columns
A | B | C | D | E | F....
SoftwareName|AssetID|Edition|Qty|Price| etc.


Each destination file is per SoftwareName. From the destination file, I want to populate this table of the related columns based on Software Name.

Unfortunately, both Index / Match and VLookup will only found the first occurrence of many based on the software name.

INDEX('[SoftwareNames.xlsx]SWNameFeed'!$A$1:$Z$999999,MATCH($A3,'[SoftwareNames.xlsx]SWNameFeed'!$A$2:$A$999999,0),MATCH(D$1,'[SoftwareNames.xlsx]SWNameFeed'!$A$1:$Z$1,0))

Obviously, when I do use COUNTIF($A$2:$A$99999,"ABC Software") it will display the total number of occurrences found

Is there a Index / Match type command / function that works similar to COUNTIF() and populate each row with its related unique data (i.e. AssetID, Edition, etc.)
Any assistance would be greatly appreciated.
Respectfully,
Tom