Copy all "active" data from a worksheet from every workbook in a folder.

Hello :>

I am in need of a macro which will run in document CopyBriefs

It will copy the "active" lines of a Title Summary worksheet on every SKU document in a folder.

I have provided a sample SKU document and CopyBriefs document

Please note, the Title Summary worksheet sheet is a reference to the Brief worksheet. So when I say "active" lines I mean the lines which aren't referencing blank/0 values.

(Sample provided would be lines 2-7 Excluding headers)

Copy Briefs.xlsm Test SKU.xlsx

Thank you for your time.

Thank you for the response,

Hi Arlette,

I would like to run a macro from CoppyBriefs.xlsm, which will open every SKU file in Y:\2013\~Product Briefs\01. January\

(Sample is Test SKU.xlsx) and copy the contents from the Title Summary sheet.

This information could be any amount of rows, so I would like to copy only the active data. (In the sample Test SKU.xlsx there is a Header, a Parent line (line 2) then the component lines below it, these are a reference from the Brief work sheet and could be a varied number of rows)

So in the sample I would copy rows 2-7 on Title Summary.

Hi Jerry, apologies. I left the Current Macros I had in there as a reference. They currently work very well, you might recognise the macro from http://www.excelforum.com/excel-prog...workbooks.html
But it only copies with header line, so isn't *quite* what I am after.

Hello, I don't suppose there are any suggestions after the new information supplied?

The issue I'm having is with the code to copy only the rows of data that do not equal blank/zero.

Hi Arlette,

each [Test SKU.xlsx]Title Summary! sheet will copy into [Copy Briefs.xlsm]Product Brief! I hope this helps clarify.

Hello, the code below isn't quite working. But I feel it's very close, please advise if this is any help

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is copying data, but not the correct data. I believe due to the "NR"

Because it has NR= NR+1 this is just going to 1 row down on each change in file correct? But each file could have multiple lines. I need to be pasting to the first empty row on this workbook.sheet wsOutput = ThisWorkbook.Sheets("Product Brief")

When I had it set to (xlup) originally. The Macro cycles through each file and when I look at the end result, it is blank.

I tried again with the same outcome.

Hi Arlette, thank you for the update.

I believe there are issues because the 'Title Summary' contains links to the 'Brief' Sheet.

So in the master document after the macro is run, one of the cells shows.
='Y:\2013\~Product Briefs\01. January\[BHE4490 UFC Presents the Ultimate Fighter Season 11.xlsx]Brief'!$D407

Can I please have the code you supplied to include an option for open as ReadOnly:=True and also to Paste the data with Formatting and Values?

Hi Arlette, the reasoning for opening as read only was due to each file having a warning to open as read only set. Apologies I did not specify this was the case earlier.

Please Login or Register  to view this content.

Compile Error - Syntax Error displays as red when I copy the code.

Thank you Arlette, that has amended the issue!

I'm getting another strange outcome, but I'm not sure if it's easy to resolve without having access to all of the files i'm copying.

It's getting through several files and then it will copy the headers of the files and the 2nd line.

When I check the files, the headers are on A1 and not A2, so i'm not sure why this is occurring!

Also is there an easy way to exclude files that begin with a ~ ?

If our Mac users open a file it creates a copy beginning with ~, which will error when it tries to open.

Please Login or Register  to view this content.

Also, is there a way to copy cell Formatting and Values?

Thank you for your help Arlette,

I have changed the code to include a copy paste special values on each file. Because the SKU\Title Summary! contained links to another Sheet, I wanted to find all cells that contained a 0 (after pasting values) in column A, then delete all of those rows.

The macro then copies the "active" lines to the Copy Briefs.xlsm\Product Brief!

Please Login or Register  to view this content.

I also needed to include a line after each copy/paste to empty the clip board, as I was receiving a warning message.