Macro to import data from one workbook into another & saving

[badlydrunkboy]

Hi,

I'm hoping this will be a fairly simple one. I've got a spreadsheet listing applications on PCs (assets), one line per application, and need to import that info into another document for each PC and save it with a file name which = the PC name (asset number). I've attached a couple of sample files to try to explain what i mean.

For asset number PC037014 I need the corresponding fields filled and the list of applications pasted under where is says Additional Software List (inserting more rows if there are too many applications to fit in). Once the information has been populated, i'd like the file to be saved in a specific folder then move onto the next asset number in the list.

I hope that makes sense.

Any help would be hugely appreciated

[xladept]

Hi Bad boy,

Name your data sheet as "Data" and your build sheet as "Template" keep them open and try this:

Please Login or Register  to view this content.

[badlydrunkboy]

That's amazing, thank you so much!!

I wonder if you might be able to help me with one other issue?

Is there any way to make the macro only create build sheets for results of a filter. For example, if i only want to create build sheets for people with cost centre 15150, i would like to be able to filter on 15150 and run the macro.

Does that make sense?

[xladept]

Hi Bad boy,

Thanks for the rep! - I used a temporary book and an array and I didn't code for the autofilter - so, if you manually filter your data:

Please Login or Register  to view this content.

[badlydrunkboy]

Thank you for your reply. I've tried using the code though and it doesn't seem to work. I get a type mismatch error at : CC = AF(i, 6)

Any ideas?

[xladept]

HI Bad boy,

You could try changing:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

[badlydrunkboy]

Hmm, when I do that I get "Compile Error: Expected array" at the AF part of CN = AF(i, 2)

This is the code I'm currently using. I made a few little tweaks like changing sheet names and defining the group so the sheets can be saved into specific folders depending on their group. I just can't get it to work with filters.

Please Login or Register  to view this content.

[xladept]

Why did you insert the "With" block:

Please Login or Register  to view this content.

The array AF must be declared as a variant, so change:

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[badlydrunkboy]

Sorry, I think that must have been my mistake in not removing the With block. I have removed it now and also changed the AF to be declared as a variant but the code is still failing with a subscript out of range error at ": LOC = AF(i, 11) & " " & AF(i, 12)"

Please Login or Register  to view this content.

[xladept]

Hi Bad boy,

Have you a different sample? My sample data sheet has only ten columns

[badlydrunkboy]

Of course. Here you go.

[xladept]

Hi Bad Boy,

I used "Build Sheet" re "Template" and I made the column limit variable:

Please Login or Register  to view this content.

[badlydrunkboy]

This is perfect. Thank you so much!

[xladept]

You're welcome and thanks for the rep!