Novice with VBA and macro writing needing help to loop a macro unti no data

**jrongone** · 07-24-2013, 07:00 PM

Greetings,

I am attempting to write a macro in Excel 2010 that pulls a lengthy number of specifications data from a master list and copy/pastes that data into new tabs. Each new tab will be for one specification (IE: Oxygen Level) and will list all the lot numbers and their respective data for that specification. I have written the VBA code to pull everything I need and copy/paste into new tabs etc., but what I cannot do, is to write it in that the macro loops or continues to pull, copy/paste for EACH lot for each specification. My macro is only able to preform the task for the 1st lot number only and then stops.

Here is a sample of my code:

Sub ComplianceMacroTest()
'
Application.ScreenUpdating = False

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Data").Range("A6:F7").Copy Destination:=Worksheets(Sheets.Count).Range("A6")
Sheets("Data").Range("C1:F5").Copy Destination:=Worksheets(Sheets.Count).Range("C1")
Sheets("Data").Range("A8:F8").Copy Destination:=Worksheets(Sheets.Count).Range("A8")
Cells.Columns.AutoFit
Cells.Columns.HorizontalAlignment = xlCenter

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Data").Range("A6:F7").Copy Destination:=Worksheets(Sheets.Count).Range("A6")
Sheets("Data").Range("G1:J7").Copy Destination:=Worksheets(Sheets.Count).Range("C1")
Sheets("Data").Range("A8:F8").Copy Destination:=Worksheets(Sheets.Count).Range("A8")
Sheets("Data").Range("G8:J8").Copy Destination:=Worksheets(Sheets.Count).Range("C8")
Cells.Columns.AutoFit
Cells.Columns.HorizontalAlignment = xlCenter

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Data").Range("A6:F7").Copy Destination:=Worksheets(Sheets.Count).Range("A6")
Sheets("Data").Range("K1:N7").Copy Destination:=Worksheets(Sheets.Count).Range("C1")
Sheets("Data").Range("A8:F8").Copy Destination:=Worksheets(Sheets.Count).Range("A8")
Sheets("Data").Range("K8:N8").Copy Destination:=Worksheets(Sheets.Count).Range("C8")
Cells.Columns.AutoFit
Cells.Columns.HorizontalAlignment = xlCenter

The "Data" sheet has all of the specifications on one sheet and lists all lot numbers in column B. The rest of the code is written to find and copy/ paste the first lot for each specification and then paste it into a new tab with the header and criteria for that specification. I am just honestly awful at macro writing but know enough to be dangerous! LOL. Any help you can provide would amazing! Thanks so much in advance.

**alansidman** · 07-24-2013, 07:03 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**jrongone** · 07-24-2013, 07:46 PM

My apologies..... please see updated and code tagged original message below.

Thanks so much.

Originally Posted by jrongone

Greetings,

I am attempting to write a macro in Excel 2010 that pulls a lengthy number of specifications data from a master list and copy/pastes that data into new tabs. Each new tab will be for one specification (IE: Oxygen Level) and will list all the lot numbers and their respective data for that specification. I have written the VBA code to pull everything I need and copy/paste into new tabs etc., but what I cannot do, is to write it in that the macro loops or continues to pull, copy/paste for EACH lot for each specification. My macro is only able to preform the task for the 1st lot number only and then stops.

Here is a sample of my code:

Please Login or Register  to view this content.

The "Data" sheet has all of the specifications on one sheet and lists all lot numbers in column B. The rest of the code is written to find and copy/ paste the first lot for each specification and then paste it into a new tab with the header and criteria for that specification. I am just honestly awful at macro writing but know enough to be dangerous! LOL. Any help you can provide would amazing! Thanks so much in advance.

**TMS** · 07-24-2013, 08:07 PM

I think the preference might have been to add Code Tags around just the code in your original post rather than repeating the content and marking it all up

I never understand why people want to create multiple sheets with the same or similar layout, formatting and formulae and the split the data amongst them. You are far better if you keep all your raw data in one table in one sheet. You can then create Tables, charts, Pivot Tables, sort it, filter it, group it, subtotal it ... whatever, you can slice and dice it however you want. Once you split it up it is much harder to bring together and analyse it ... which I suspect that you will want/need to do.

And people will add columns, delete columns, hide columns, change formulae ... nightmare.

And you're likely to need less code, if any, because the data can be processed using Excel's built in features ... which has to be a bonus if you're not very experienced with VBA.

Regards, TMS

**jrongone** · 07-25-2013, 10:10 AM

Although I do appreciate your reply, the reasons for this test project are solely learning based as I am attempting to teach myself some pertinent tools to help me with projects I will have in the near future at my current job. I know excel very well.... I work with pivot tables and trend charts and SPC data and can manipulate excel based data fairly well. What I do not know is VBA,..... so thank you for the reply, but it was not helpful at all.

Cheers!

**TMS** · 07-25-2013, 11:02 AM

I'm sorry if I have offended you and that my response was unhelpful in the circumstances.

Fair enough. From a practical point of view, I have always found it more effective to set myself a challenge and overcome each of the hurdles it presents as I encounter them. That adds pressure, obviously, but it really helps focus the mind. Trying to learn something "with a view to applying it in the future" gives you less of an incentive to learn, I believe.

This is a slightly different way of copying the data across.

It's more scaleable and maybe easier to see where the source and target addresses are.

This approach uses arrays to store the source and target addresses and you can loop through them. It is possible to enter the addresses into a worksheet and load them into the arrays ... and you could make each of them 2-dimensional. You'd loop through 1 dimension to create the sheets and the second to populate the cells.

If you step through the code, I think you will see that you overwrite the data ... I don't know if that is intentional.

Please Login or Register  to view this content.

**jrongone** · 07-25-2013, 12:15 PM

Thanks so much TMSHucks, and no worries. I didn't take offense.

I will give this code a shot and see what I can do with it. Thanks again for the lesson and the help, I truly appreciate it. I'll let you know how I make out!

**TMS** · 07-25-2013, 12:19 PM

You're very welcome. Thanks for the rep.

Novice with VBA and macro writing needing help to loop a macro unti no data

LinkBack

Thread Tools

Rate This Thread

Display

Novice with VBA and macro writing needing help to loop a macro unti no data

Re: Novice with VBA and macro writing needing help to loop a macro unti no data

Re: Novice with VBA and macro writing needing help to loop a macro unti no data

Re: Novice with VBA and macro writing needing help to loop a macro unti no data

Re: Novice with VBA and macro writing needing help to loop a macro unti no data

Re: Novice with VBA and macro writing needing help to loop a macro unti no data

Re: Novice with VBA and macro writing needing help to loop a macro unti no data

Re: Novice with VBA and macro writing needing help to loop a macro unti no data

Thread Information

Users Browsing this Thread

Similar Threads

Performing Multiple Commands in a single macro (Macro novice here!)

Writing macro that inputs data after last row, then sorts

writing a data inputting macro.

novice needing help on formula

writing a macro to produce data for different intervals

Bookmarks

Bookmarks

Posting Permissions