Create a separate workbook using data from another workbook.

Hello everyone,

I am very new to using macros in excel. I have played around a lot and I am getting close to what I need, but I am looking for some assistance! :)

Basically what I am trying to do is input a list of data on Sheet 1, have this data automatically be transferred onto Sheet 2, but skip any blank rows between data on sheet 1. Then have a save button on sheet 2 that then saves a separate workbook with just sheet 2 in it.

Thanks in advance!

Hi macro_noob3030

Probably the easiest way is not to use macros!

I am assuming that if there is data in column A, then there will be data in B through to (whatever) and likewise if there is nothing in column A then that row is completely blank.

Copy from sheet 1 A1 : last row last column to Sheet 2 B1

In column A number from 1 to last row used

Sort whole sheet on column B.

This will put all blank rows at the bottom of the data. Delete these rows

Sort whole sheet on column A (to put back in original order)

Delete column A

Delete Sheet 1

Save As (New name)

If you would like this automated by means of a macro, let me have a sample of your data and I will write such a macro

Regards
Alastair

Hi Alastair,

Thanks so much for your help! This is the kind of data I want to automate.

Sheet 1 contains the following data:

Capture.PNG

From this sheet, the data I care most about are 'Code' and 'Qty'. I am basically trying to transfer these two columns from sheet 1 to sheet 2 to create the following:

Capture2.PNG

So the list has just the data without any of the spacing from sheet 1. Then I want to save sheet 2 as a separate excel workbook that contains just sheet 2.

Is this possible?

Thanks for your help in advance!

Hi macro_noob3030

I have just come back to the forum after a year's break. I have discovered that I have forgotten a lot of syntax!

Anyway - here's a couple of files that should fit the bill. Let me know how you get on.

If you have not run macros before, you will need to set preferences: File>Options>Trust Center>Trust Center Settings>Macro Settings>Check "Enable all macros"

Regards
Alastair