Macro to copy data from specific columns and rows to new worksheet.....

Hi all.. I am new to forms in general and I hope I can explain what I am trying to accomplish. What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be helpful.

Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.

Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
4) repeat requirement 3 till no more Scenario's

Is this doable? Can someone help me out?

Thank you

I was not sure if it was confusing or not, you answered my question. Let me put it this way.
1) Copy the following data below to sheet1 in excel.
2) Create a macro where any column that has the number 1 in row 1 will copy all the data to a sheet named Macro1.
3) Any column that has the number 2 in row 1 will copy all the data to a sheet named Macro2.
4) And any column that has the number 3 in row 1 will copy all the data to a sheet named Macro3.

The data in sheet Macro1 will say "Copy this to worksheet Macro1."
The data in sheet Macro2 will say "Next all data in Macro2."
The data in sheet Macro3 will say "Thank You."

1 1 2 1 1 3 2 2 1 3 2 2
Copy this Next to worksheet Thank all data Macro1. You. in Macro2.
Copy this Next to worksheet Thank all data Macro1. You. in Macro2.
Copy this Next to worksheet Thank all data Macro1. You. in Macro2.
Copy this Next to worksheet Thank all data Macro1. You. in Macro2.
Copy this Next to worksheet Thank all data Macro1. You. in Macro2.
Copy this Next to worksheet Thank all data Macro1. You. in Macro2.



Does this explain it better?
Thank you

I was not sure if it was confusing or not, you answered my question. Let me put it this way.
1) Copy the following data below to sheet1 in excel.
2) Create a macro where any column that has the number 1 in row 1 will copy all the data to a sheet named Macro1.
3) Any column that has the number 2 in row 1 will copy all the data to a sheet named Macro2.
4) And any column that has the number 3 in row 1 will copy all the data to a sheet named Macro3.

The data in sheet Macro1 will say "Copy this to worksheet Macro1."
The data in sheet Macro2 will say "Next all data in Macro2."
The data in sheet Macro3 will say "Thank You."

I can do that. I uploaded a workbook named Test Scenario-2.xlsx with some data and the expected results in the additional worksheets. It only goes from A-M but the original goes from A-EF and unfortunately is still growing. I hope this helps out.

Thank you.

You could try this on a copy of your workbook.
Don't know how long it'll take on your original file.

Please Login or Register  to view this content.

Hi guys, thank you very much for helping me out. I tried to copy and paste your code into the workbook and found out I had no clue on how to do it. I am a bit embarrassed over it to tell you the truth. I figured it would have be a lot easier.

I spent the rest of the afternoon yesterday Googling what needed to be done. I did figure out how to get to the developer tab. Then I clicked on the Visual Basic icon. When I get into VBA, I am not sure what to do there. Do I double click on Sheet1, Macro1, or ThisWorkbook. I was thinking of just starting a macro, then exit out, then copy your code in its place, but that did not work.

Again, thank you very much for being patience.

WHOA!!! This is amazing. Bowing to the VBA Gods.... (stroking ego hehe)

So as I look at the workbook you have four nice macros with one that clears the results sheets... that is awesome!!

Can we shoot for lvl2 now? Maybe more challenging, but I have a feeling you guys would say, Bring It On! For the test data I set Row 3 (Macro #) to be a one to one value for its Macro Tab. So we agree that #1 goes to Macro1 tab, #2 to Macro2 tab and #3 to Macro3 tab.

But in my larger workbook, some of the columns are assigned to multiple Macro Tabs. I am modifying the test data where five of the columns have been assigned to multiple columns. At this point there is only a need for a column to be copied in to TWO macro tabs, but you never know with management and how they want their matrixes. It might turn into a column to three or more tabs. I am very open to suggestions as to how you would need/used the contents in the cell to determine this, instead of x, x (1, 3).

Thank you again.

This is for two different macro sheet names.
If, like you mentioned, going to have three or more you'll probably start working with the sheet names in an Array.
Try the attached and see if that works for you.

Play with the attached for multiple pages.
It does not have any error checking in case Sheets don't exist in your workbook.

This should take care of Column A not being used and of the possibility of a Sheet missing or no Sheets at all.
Maybe one, or more, of the experienced people on this forum wouldn't mind commenting about things not being proper.
Replace the code in version 3 with this code or rename the other code and try this one in that workbook.

Please Login or Register  to view this content.

Thanks jolivanes, this is awesome. I have been playing with your final code and had a few questions. First let me answer your last one.

You stated:

If, like you mentioned, going to have three or more you'll probably start working with the sheet names in an Array.

To tell you the truth, I envisioned this to be multiple macros. I figured Macro1 (unknown name at this point) to tab Macro1. I did not plan on naming all the tabs as Macro because it does not define what is in the tab for others to read. Tab names would be defined as Heading, Requirements, Availability, etc. Now that you mention an Array, I don't know where to go with it. So far the Macro Tab is working for me, but I know what they are referenced to, today. Give me 6 month and I will need to read this thread......


As for the code you added in the thread, I have been playing with that. The Column A not being used would have been an issue. That worked great. Also the missing or misspelled macro tab was a great idea. I did have one of the tabs misspelled, can you believe that..... thank you.

I copied all the code into my original workbook and realized I did not make the test scenario workbook (what we been working on) and my original workbook exactly the same in rows and columns. For example the numbers in the "Macro" row in our test data starts at 3,B and in my original it starts at 5,B. My Heading is at 7,B and all data starts at row 23,B in my original.

So I decided to try and pull apart the VBA code so I could understand it better but having a bit of a problem. So far I came up with changing the number 3 (which to me determines the row) with the number 5 through out the code. That did help in copying the data but not completely. I have found some alignment errors, such as an added cell where a row was dropped down one cell.

Have not figured out where the Heading row (row 4) data is being copied specifically in the code.

Issue with the Clear_Macro_Sheets() section. When I used the code to copy data, it copies all the content including the color, borders, etc which is great, help me see it a lot easier. But the clear script does not remove every thing. Only the data is removed leaving the color and borders which will over lap and confuse when modifications are made. I been doing a cut then delete to clear each worksheet individually, but was hoping there is an easier way.

I was wondering, is there a way to put comments in the code to understand what's happening? Some type of explanation of what's being done? As I have mentioned previously, this is a living document and I definitely see modifications being made as time goes on.

Thank you....

Regarding the clearing of the "Macro" sheets.
If you want all the formatting, coloring etc, deleted also.

In the Clear_Macro_Sheets code, replace this

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

or replace the whole macro with this.

Please Login or Register  to view this content.

Think about it and let us know.

I fixed a few issues I was having.
1) When pasting data, the first column is no longer blank. I saw how you had it

If Left(ws.Name, 5) = "Macro" And ws.Range("A1").Value = "" Then ws.Columns("A:A").Delete

and changed it to

If ws.Name = "Macro" And ws.Range("A1").Value = "" Then ws.Columns("A:A").Delete

2) Since I wanted to have separate macros to clear individual sheets, I did the same changed

For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name, 5) = "Macro" Then ws.UsedRange.Delete (xlShiftUp)
Next ws

to

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "TIA Questions" Then ws.UsedRange.Delete (xlShiftUp)
Next ws

Still have not figured out a few things. This is what I am stuck on:
1) When copying data, if there is a blank cell in the Scenario data, the macro will jump to that empty cell, missing all data before it, and copy only from that blank cell to the end.
2) When copying the data, I am copying all the data in the array. I have not figured out how to copy just the data for all 1’s to Documentation Requirements, 2’s to Documentation Availability, 3’s to Project Information for TIA and so on.
3) When pasting data, the macro is appending to the existing data. I need it to clear or overwrite existing data. This was discovered after I created the macro. I have not tried to add this in yet.

Sending an updated testing workbook.

Thank you.

Please Login or Register  to view this content.

You don't have a Sheet called "Macro". They all have a number following the name.
This has to be taken in consideration.

Please Login or Register  to view this content.

If you go with code for each separate Sheet, you don't need "For Each ws in etc etc".
Just a

Please Login or Register  to view this content.

Re: 1
This part of the code copies from the last used cell in that Row to the first empty cell up it encounters.

Please Login or Register  to view this content.

You can:
1) Make sure non of the Cells are empty.
2) If the start of that Range is always the same (here Row 21), hard code it into the code.
3) Find the String "Scenario 1" and use "Offset" in the code.
There are more possibilities but these are three that come to mind.

Re: 2
Does that mean that the 1's do not go into Sheet "Macro1"?
Just in differently names Sheets? Or both?

Re: 3
Should be no problem. As part of the code you can clear the Sheet first.

Once when your setup is to your liking and things are not going to change anymore,
let us know what needs changing and we'll figure something out to make it work properly.

Good luck

Hey jolivanes

I guess I pasted the code incorrectly. I will try and not reply with code since its obvious I don't know what I am doing.

After reading your response's, I was a bit confused but I will try and reply to them:
1) Unfortunately cells that are empty must stay that way. I can not fill them with anything.
2) I believe hard coding the Range would not be an issue, as long as I know where in the code I can change that. This way if an additional Row is added, I know where to make that change.
3) I could not find any String with the name "Scenario 1" to do an "Offset". Did you mean something else?
4) The Macro1 - Macro4 sheets are not going to work. I mentioned previously:

I did not plan on naming all the tabs as Macro because it does not define what is in the tab for others to read. Tab names would be defined as Heading, Requirements, Availability, etc.

They will be differently names Sheets?
5) I tried to do ws.Name.Cells.ClearContents and that did not work. I also tried to put something inside the For... Next Loop and the data kept getting deleted every time. Then my head started to hurt.....

I am pretty sure there are no more surprises. You have the expected results in the correct worksheets and the worksheets are named correctly.

Macro Sheets 1 - 4 are not needed and can be removed.

Re 1:
No problem.

Re 2:
No problem. Will make it variable length ranges.

Re 3:
Sheet "TIA" cell A15
That seems to be, in your example anyway, in line with the top of the range to be copied.

Re 4:
No problem
As long as you have a relationship between a cell in "TIA" sheet and sheet names.
In other words, need to know from a cell value in Sheet "TIA" where (which sheet) to copy to.
Currently it is Row3 and/or Row4.

Re 5:
Don't know offhand what you are refering to although I suspect the clearing of the sheets before
you paste into it.
Is no problem though.

If I have a bit more time here in a while I'll see if I can make a book, based on your current example, with some different ideas

It is just an idea.
You have to select a cell, any cell will do, in the column that you want to transfer.
For instance, if you want to transfer the information from Column B, select any cell in column B and
click on the macro button. In the Listbox that pops up, select one or more sheets.
Click on the "Transfer" Button
The relevant Data from the Column you selected should now be copied into the Sheets that were selected in the ListBox.
As it stands now, it clears all the Sheets before the transfer so you will have only one Column in the selected Sheets.
That can be changed of course.
The "TIA" Sheet set-up is also still from the previous example.

With changes you want, is this something we can build on?
IMHO, that would be better ten have a multitude of macros.

I like the concept of the one button idea but I want to make sure I understand this was just an example. Showing one button, with the option to select the worksheets you want populated instead of multiple buttons. Am I correct?

I ask because my expected results is nothing like what you have.

Yes, just an idea to show that you would not need a multitude of buttons/macros.

I like that it makes it a lot cleaner. Sure ... go for it.

I made my own type of requirements doc that I upload. I am not sure if it will help out.

Post #15: I did not plan on naming all the tabs as Macro because it does not define what is in the tab for others to read.
Post #18: I need the sheet names to be specific and not macro1, macro2, etc.
In Post #20 you attached a Workbook (that was your latest excel attachment) with 4 Sheets named Macro1, Macro2, Macro3 and Macro4.

Could you attach your latest workbook that is up to date with the sheets filled in that need to be filled in from "TIA" sheet.
Also put in some short concise explanations with it.

Here is the latest workbook I have. I have added additional worksheets that I know I will need. I also know that will grow but do not know the name of the worksheets or the data.

I think this is what you were asking about concise explanations.

1) The name of the worksheet comes from row 2. This is a fixed row and never changes.
2) The first row in the copied to worksheet comes from row 5. This is not a fixed row and may be moved down additional rows.
3) All data is copied from any row with the name Scenario in Column A. This is a fixed column.
4) All cells shall be copied to the new sheet including, blanks, font, border and color.
5) The relationship of Macro# to sheet is one to one. There will be more numbers for the additional sheets added.
a) When number 1 is found, the data in that column is copied to Documentation Requirements sheet.
b) When number 2 is found, the data in that column is copied to Documentation Availability sheet.
c) When number 3 is found, the data in that column is copied to Project Information for TIA sheet.
d) When number 4 is found, the data in that column is copied to TIA Questions sheet.
6) All data copied to new sheet starts at cell A:1. This is fixed.
7) Data stays in the worksheet until the macro for that specific worksheet is run again. No clearing of other worksheets when running a specific worksheet.
8) Data copied to a worksheet shall overwrite existing data (or maybe stated you are overwriting data, do you want to continue), not append to the existing data.

Is this more or less it?

WOW !!! NICE!! That is it exactly.

Unfortunately I am having problems getting it to work on the original. Copied the Modules, and UserForm. Figured out there was code in the UserForm, right click then View Code....that was cool. Not sure if there is something I missed. Any tips or hints you can give me?

Also is it possible you could but the comments in again so I can follow along. I added some already.

Unfortunately I am having problems getting it to work on the original.

Doesn't tell me much. Are the two workbooks the same?
Google "How to export and import Userforms in excel" if that's where the problem lies.
If not, sanitize your original workbook and attach it. No private sensitive data left in it

Please Login or Register  to view this content.

Unfortunately I am having problems getting it to work on the original.

Have you tried copying the sheets from the original into the attached workbook after deleting all but the first four sheets in the attachment?
You have to make sure that all the names are exactly the same. No trailing spaces etc.

Let us know how things progress.

Regarding your PM and your HD crash.
When you're back up to speed, attach your workbook that you can't get to work.
Sanitize it and make sure it isn't too large.
Good luck
John