Adjacently Copy Values to a new sheet

**NaNaBoo** · 03-16-2009, 01:57 PM

Good Afternoon,

Can any one help me with a code that will allow me to copy values from one sheet and adjancently paste them into another sheet?

I would like to have theses values be pasted right under the next available blank line.

Specifically, I would like a code that will select a particular range and pasteonly the cells with values to the Sheet1 on the next available blank line. And select another range and adjacently paste only the cells with values to the Sheet1 on the next available line, and so on.

I have attached a sample.

Thanks.

**dustycrockett** · 03-16-2009, 04:22 PM

This code will cycle through all the sheets in the workbook, copying the data from each "project" sheet to a new row in the matching quarter on the "combined" sheet.

Please Login or Register  to view this content.

**siamadu** · 03-16-2009, 05:11 PM

Awesome!!!It worked!!

dustycrockett,

The only problem that I need to be tweaked on the code is to not have the additional rows added. When I run the code, it copies the values and adding additional rows. I would like to only copy the the specified ranges.

Any thoughts?

**dustycrockett** · 03-16-2009, 05:16 PM

Originally Posted by siamadu

Awesome!!!It worked!!

dustycrockett,

The only problem that I need to be tweaked on the code is to not have the additional rows added. When I run the code, it copies the values and adding additional rows. I would like to only copy the the specified ranges.

Any thoughts?

oops...that can be stopped by deleting this statement:

Please Login or Register  to view this content.

I only added it cause I just hate it when I have to go through in advance and manually count how many rows I'm going to need.

**NaNaBoo** · 03-16-2009, 05:24 PM

Dustycrockett,

I just tested your code and it worked perfectly. However, I see someone beat me it. As for me, I preferred you original code.

I like that it adds the new lines.

My other question is could this code be modified to use other names besides "Project"? Could I rename the "Project1" and Project2" to something total different and still have the code work?

Thanks!!!

**dustycrockett** · 03-16-2009, 06:08 PM

I didn't even notice that the other response was a different user.....

As written, it looks for sheets whose names begin with "Project", but if you just wanted to process every worksheet (that follows the summary sheet), you would
1. Make certain your summary sheet is first.
2. substitute this:

Please Login or Register  to view this content.

for

Please Login or Register  to view this content.

3. substitute

Please Login or Register  to view this content.

in place of

Please Login or Register  to view this content.

4. substitute

Please Login or Register  to view this content.

for

Please Login or Register  to view this content.

delete

Please Login or Register  to view this content.

and
5.take out the last "End If" near the bottom, so that

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

that should do it.

**NaNaBoo** · 03-17-2009, 08:39 AM

Hi,

I tried to use the code, but some more questions came to mind when I was making the changes suggested below. If I wanted to use this wonderful code in another workbook would it be possible? Is there a way to make it more generic?

Here are some examples:

Could I somehow substitute different worksheet names in the code line

Please Login or Register  to view this content.

Like this

Please Login or Register  to view this content.

Could I replace "qtr" with other strings

Please Login or Register  to view this content.

Like this

Please Login or Register  to view this content.

Also,

Is it possible to copy specific ranges on a worksheet to "Combined Projects"

Here is another example:

Could I substitute

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

I have revised my previous attachement some that you can get a better understanding of what I am trying to say.

**NaNaBoo** · 03-19-2009, 10:02 AM

Hi, Duckett.

I'm I doing this correctly?

**NaNaBoo** · 03-19-2009, 01:13 PM

Can anyone please help?

It seems as though the person who was helping me has not been on the forum lately.

This is my first time using VBA

**dustycrockett** · 03-19-2009, 07:13 PM

Sorry, tied up with work for a couple days.....

the modifications I described in my 2nd post is the most generic -- it goes through and copies data from every sheet, pasting it onto the 1st sheet, irrespective of the name of the worksheet.

Your first proposal, With Worksheets(FAA, DHS, HHS) is not valid syntax. To specify the worksheets you want to process, try this:

Please Login or Register  to view this content.

The statement For Each sh In Worksheets causes the program to perform all the steps until it reaches Next, for the first worksheet, and then repeat those steps for every worksheet in the workbook. sh in that context is an arbitrarily chosen variable name that designates the worksheet being processed.

The With sh statement is just a shorthand way to refer to an object in the program code statements; in my example, it enables you to use .Cells instead of sh.Cellsbetween With and End With.

Your second proposal should work just fine, except note that If Left(.Cells(pr, 1), 3) = "This Week" or "30 Days" Then
should be If Left(.Cells(pr, 1), 3) = "This Week" or Left(.Cells(pr,1),3) = "30 Days" Then

the answer to your last question is yes, that would work fine.

**NaNaBoo** · 03-23-2009, 10:21 AM

Here is my interpretation:

Please Login or Register  to view this content.

It is definately not working right. The cells are not pasting the contents from each sheet to the right sections on the Combined Sheet.

Please bear some patience with me, I am clueless to programming with Excel. Can you help me to revise it!!

**dustycrockett** · 03-23-2009, 12:17 PM

If you haven't been using the "Step Into" feature of vbasic, I highly recommend it, to help you figure out what the program is doing step by step.

Here's a couple things I noticed just reading through your code, without actually trying to run it...

This:

Please Login or Register  to view this content.

was a mistake on my part in the second revision -- If Left(.Cells(pr, 1), 3) should have been deleted -- it just means "counting from the left hand side, take the first three characters from the contents of the cell located at row pr, column 3". In the original, you wanted to find a row that contained "QTR" in the first three characters, now you're looking for the whole phrase.

this part

Please Login or Register  to view this content.

was designed to pick up which quarter the work was being done in: in other words, after finding "QTR", it just picked up the last character in that cell (similar to "Left(..." above). It worked because your sections were consistently named QTR 1, QTR 2, etc.

In your modification, you can just use qtr = .Cells(pr,1) instead, then in the next section, you will search for qtr.

Please Login or Register  to view this content.

By searching for either "This week" or "30 days", you were telling the program to paste the data into any section, regardless of which section it came from. Instead, you want to find whichever specific section the data came from. If that makes sense.

I can't believe that this statement:

Please Login or Register  to view this content.

would work at all. You're copying a range that is 15 rows by 4 columns, pasting it to a range that is 1 row by 3 columns.

It looks like every time you encounter "This week" or "30 Days", you want to copy the same set of data (rows 3 to 9 and 12 to 41 from columns A through D). Can't imagine why you would want to do that.

**NaNaBoo** · 03-23-2009, 12:54 PM

I apologize in terms of my high ignorance level of programming in Excel. I know I may have confused you (Sorry about that

). And you have really helped me a lot. Let me revisit what I am trying to accomplish

1. The new workbook I posted replaced the “Qtr1 and Qtr2” with “This Week” and “30 Days.” How will the code be revised to accept the two new variables?

2. The new workbook replaced “Project1” and “Project2” with “FAA”, “DHS,” “HHS.” How will the code be revised to accept the three new sheets?

3. I will like to only paste the values to "CombinedProjects", not colors.

I made an error when I requested a specific range because you have already provided me with the ability to copy the values to the appropriate section of the “Combined Projects”

The reason why you see the mistakes in the code, I do not know what I am doing if my life depending on it. So if I try to decipher the code the way it is, we will be working on this forever.

If it is not too much to ask of you, is it possible that you can revised the original code and posted it back. And then I can study the code with the changes to know what is going on.
I am sorry for being such a burden.

Please believe me, I really want to learn. I will try the “Step In To” and also want to know how you learned how to program these difficult programs? It seems nearly impossible.

**dustycrockett** · 03-23-2009, 09:00 PM

Don't feel bad about inexperience, you'll get it soon enough. I learned this language slowly over many years, mostly by using the "Help" screens, although the Excel2007 Help facility doesn't seem as userfriendly as previous versions. I use "record macro" sometimes, but as you may have discovered, it's very limiting.

Another useful feature is the "watch" screen -- select "Debug" from the menubar, then click on "Add watch" and type a variable name (or any expression), it shows the changes in that variable as you step through the program.

I think the code below should work for your revised spreadsheet; I highlighted in red the changes from the original code. I didn't try to test-run it, so you may find a mistake or two. If you can't make it work, let me know, I'll take a closer look.

Please Login or Register  to view this content.

**NaNaBoo** · 03-25-2009, 09:04 PM

Dustycrokett,

This code actual worked great!!!
I was trying to use it in another spreadsheet and I noticed two problems I was having with it. with the that

1. It was inserting an EntireRow, which erased my forumlas
My question is, can you revise this code to only have the values copy in columns C:AJ?

2. It copies the values in the first sheet, then copy the values in the second sheet over the values in the first sheet.
My quesition is why is it not looking for the next empty row?

Thanks for all of you help. I really appreciated.

**dustycrockett** · 03-25-2009, 11:15 PM

can you revise this code to only have the values copy in columns C:AJ?

whereever you see ...Cells(... the first number inside the parenthesis is the row number, and the second is the column number. In this program the row numbers are all represented by variables, but the column numbers are all 1 or 3 (column A or C). Range(Cells(pr,1),Cells(pr,3)) means cells A1 to C1 (when pr = 1). So you can change those column numbers to whatever you want.

why is it not looking for the next empty row?

I suspect one of two things --
see the part that says "Do While compro.Cells(cpqr, 1) <> ""

It's looking for a blank cell in column A -- if the data your new spreadsheet starts in column C, it won't see it if A is left blank.

Either that, or else it's this line right here:

Please Login or Register  to view this content.

Here, the variable "cpqr" is a variable that tracks where you are on the consolidation worksheet.

It means "Find the next occurence of qtr (whatever the value of that variable is) on the consolidation worksheet, starting after the last row you pasted to" and store that row number plus two in the "cpqr" variable.

You can read about "range.find method" in Excel help.

It worked with the original file because we were looking for "Qtr1", "Qtr2", etc., and knew which order they would be in, but it's probably messing you up now. I think if you just replace the purple text with "A1:A1" and drop the " + 2" from the end of that line it'll work.

**NaNaBoo** · 03-25-2009, 11:51 PM

Thanks for you response. I really appreciated.

What you just sent me is very informative and now i know what is going on with the Row and the columns. At least that is a starting point for me trying to comprend this language.

I have not tried you suggestion, but you are right when you said that data starts in column C and column A is empty.

i wanted to tell you that in the post but I did not want to make things too complicated when I was trying to explain what was going on.

Let me try to change the first few things you mention and I will post back with questions. Be prepared, because i will have questions.

But I like the way you are trying to teach me along the way.

**NaNaBoo** · 03-26-2009, 08:47 AM

I suspect one of two things --
see the part that says "Do While compro.Cells(cpqr, 1) <> ""

It's looking for a blank cell in column A -- if the data your new spreadsheet starts in column C, it won't see it if A is left blank.

You got it!! That is exactly what is happening. Column A is blank and my data that I want copied starts at column C and ends at column AJ. Can this be fixed like this?

Please Login or Register  to view this content.

**dustycrockett** · 03-26-2009, 10:54 AM

the fix for that is to change "Do While compro.Cells(cpqr,1)" to "Do While compro.Cells(cpqr,3)", so it will look in the third column (column C) instead of column A.

this: "cpqr = compro.Cells.Find(qtr, compro.Range(A1:A1))).Row + 2" is probably a good idea anyway, except A1:A1 should be enclosed in quotes "A1:A1".

You might want to experiment, try different things, see what works.

**NaNaBoo** · 03-26-2009, 11:01 AM

I have made the changes that your requested.

This is how the code looks now.

Please Login or Register  to view this content.

It is copying the data as it should. Aftering doing the testing , the only problem is that I need to have information in Column A in order for it to copy.

Is there a way I can have another macro enter data in column A only when there is data in column C?

Or can this code be revise to skip over the blank cells in Column A and search for data in column C?

Adjacently Copy Values to a new sheet

LinkBack

Thread Tools

Rate This Thread

Display

Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Re: Adjacently Copy Values to a new sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions