Copy partial row data to range on other worksheet if true

December 30, 2011 at 11:07:21
Specs: Windows 7
I've just started working with VBA, and while it sort of makes sense, the project I'm working on is over my head and I can figure out how to piece together the code. I'm using Excel 2010. Here's what I am trying to do:

- Worksheet A has a list of items with pricing and descriptions, and each row has a checkbox assigned to the "a" cell of that row.

-Worksheet B has a blank range.

-When a checkbox on worksheet A is checked to be true, I would like it to populate the empty range in worksheet B with the coordinating row's cells B-E.

I don't care whether it does this as soon as the box is checked (I kind of got this to work with "if true" formulas, but I couldn't figure out how to get it to populate the next open row) or if once everything is checked, there is a button that can be pressed to populate the range. It can't create new rows, because that would effect the rest of worksheet B - I really need it to just populate the range.

Also, if this works, how would the macro react if there wasn't enough room in the range for all of the rows? I appreciate all the help in advance!

Edited to add clarification:

Basically I would like to automate two steps. 1) to search for the "true" value in column A on Worksheet A and 2) to take cells B-F of the "true" rows and add them to a range (A6-H38) on Worksheet B. I tried to do this with IF but couldn't get it work, so I started trying to create a macro for it.


See More: Copy partial row data to range on other worksheet if true

Report •


#1
December 30, 2011 at 11:27:46
The code used to find the last row with data in a column is as follows.

lastRw = Range("A" & Rows.Count).End(xlUp).Row

Note: This code assumes that Column A is the >= the longest Column in the spreadsheet. If that's not the case, e.g. if a different column is "longer", simply have the code check that column instead.

This code instructs VBA to start at the very bottom of the specified column ("A" & Rows.Count) and then "look upwards" until it finds data in that column. It then returns the Row in which the data was found.

To find the next empty Row in that column, just add 1:

nxtRw = Range("A" & Rows.Count).End(xlUp).Row + 1

re: Also, if this works, how would the macro react if there wasn't enough room in the range for all of the rows?

I'm not sure I understand this question. Are you trying to squeeze data in between rows that are already populated? e.g. you have data in Rows 1:10 and Rows 21:30 and you're trying to put the copied data in Rows 11:20?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 30, 2011 at 11:50:42
DerbyDad03, I've already found the code to find the next empty cell, the issues that I am running into is that a) I don't want the entire row copy/pasted, just a portion if it, and b) that I don't want to populate the sheet based on empty rows, just a specific named range (Range1) within the sheet. There is other data to the right of and below the specified range, so I don't want it to look at that information when it's populating. It has to paste the values, not just insert the row, because that will cause conflicts with the other data on the sheet.

As far as the other question, I just wanted to know what would happen if more rows were selected and would populate the range than the range had rows. So, if 10 rows were selected, but there were only 9 rows in the range, what would happen? I guess I can figure that out by trial and error, but I am really lost on getting any bit of code to work the way I would like it to. Hopefully that makes sense - thanks for the help!


Report •

#3
December 30, 2011 at 16:18:45
This doesn't make sense:

"Take cells B-F of the "true" rows and add them to a range (A6-H38) on Worksheet B"

First, I don't know what you mean by "add them to a range". Do you mean "paste them into the range, presumably in the next empty row in that range?

Second, Columns B:F is 6 columns. Columns A:H is 8 columns. Do you see my confusion with that?

re: "I just wanted to know what would happen if more rows were selected and would populate the range than the range had rows. "

Well, typically when you copy/paste a range with VBA, the paste destination is a single cell, not a range, so the code won't know that there isn't enough "room" unless you write code to check first.

For example, this code is going to paste B1:F10, a range that is 2 columns wide and 10 rows long, into Sheet2 starting in A5. It's not going to care if there is data in A7 or B9. It's not even going to fail. It's just going to Paste it.

Sub CopyRange()
    Sheets(1).Range("B1:F10").Copy _
       Destination:=Sheets(2).Range("A5")
End Sub

If you don't want it overwrite existing data, you either have to check the destination range before you paste it or use Protection so that the cells can't be overwritten.

If you use Protection, then the code will throw up an error when it tries to paste something into the Protected range. You would then use an Error Handling routine to deal with the error.

As far as copying just Column B:F of a given row, the code would look something like this:

Sub CopyRow7()
 myRow = 7
    Sheets(1).Range("B" & myRow & ":F" & myRow).Copy _
       Destination:=Sheets(2).Range("A5")
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 30, 2011 at 18:26:43
By the way, they unfortunately took this great example of how to use .Find in VBA out of the Help files when they moved beyond 2003.

This might help you find all of the TRUE's in Column A that you looking for:

Example
This example finds all cells in the range A1:A500 on worksheet 
one that contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 4, 2012 at 12:44:37
Thanks again, DerbyDad. I'm piecing together some different codes to try out in my test workbook, including the ones you've provided. Sorry that I've not been as clear as I would like to be - again, all of this is new to me and I don't know what words or terms I'm supposed to use. As far as the mismatched columns - you were right! I needed to add two to my list, so now I'm moving the same number as there are on the range. Regarding the range - I've basically just assigned a name to the range of cells A6-H38. I thought it would be easiest to refer to the name of the range (Range1). Thanks again for your help and patience with someone just learning.

Report •

#6
January 6, 2012 at 10:27:18
Okay, this just isn't making sense to me. I think I'm looking at it too much like CSS, which is the only code that I am comfortable manipulating, and I cannot get this to work in my specific workbook at all. I'm having a really hard time customizing the code to my workbook, and knowing where to put the code in VBA. Please help? This is what I've tried to piece together:

1. Run the macro every time a value in column A on worksheet "Job Pricing" changes. (true/false are the only options.) For this I tried to use this code here: http://www.mrexcel.com/articles/mac...

2. If the value in column "A" on the Job Pricing worksheet is true, then copy cells B:H of that row with the true value in column "A" to the next empty row in range A6:H38 (which is assigned the name "Range1") on worksheet "Job Workbook". I've tried to do this with the code you've provided, DerbyDad, but I can't figure it out. If you could put together all the code I need, I would be eternally grateful!


Report •

#7
January 6, 2012 at 12:09:50
1 - If this is to be used as an WorksheetChange Event macro, then it goes into the sheet module for the sheet in which the change is made.

Right Click the sheet tab for the sheet and choose View Code. The macro goes into the pane that opens.

2 - Your OP mentions "CheckBoxes". To be honest, I've never worked with CheckBoxes so I can't offer any code that uses a Checkbox change to trigger an event, although I know that VBA procedures do exist for that type of event.

I also know that there are different types of Checkboxes. I think they are called a CheckBox Control and an MSForms Checkbox.

Per this site (found via Google) is appears that only the MSForms Checkbox can have an event associated with it.

http://stackoverflow.com/questions/...

I'm sorry, but you are going to have to Google around and read some the sites that show various ways to handle changes to Checkboxes since it is outside my scope of knowledge and I don't have the cycles to teach myself how to use them at this time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
January 6, 2012 at 12:21:23
DerbyDad, I don't need the macro to look at the checkboxes, though. The checkboxes are linked to the A cell at the beginning of each row, and checking the box just populates that cell with "true". If it's unchecked, it's "false". I was under the assumption that I could get the macro to look at the cell value and find the "true" ones. I HAVE been googling this and trying everything to get this worked out, I just don't understand how to put together and edit the code to look at my data. I've been doing this for over a week now, and nobody seems to be able to help me. I don't understand why some people get their code custom written out for them and all I get is vague answers and get told to "google it." I'm sorry, but I feel like I'm not getting any help here at all and what I'm looking for s being ignored.

Report •

Ask Question