Copying of cells in a row from one sheet to another.

[alazar]

The code I have written below copies an entire row of cells from one worksheet to another but what I need to do is copy certain cells within the row to another worksheet.


For RowIndex = 1 To 250
Set curCell = Worksheets("Products").Cells(RowIndex, 1)

If Left(curCell, 6) = ProductFamily Then
Worksheets("Products").Cells(RowIndex, 1).EntireRow.Copy
Selection.EntireRow.Insert
Else
If Left(curCell, 7) = ProductFamily Then
Worksheets("Products").Cells(RowIndex, 1).EntireRow.Copy
Selection.EntireRow.Insert
End If
End If
Next RowIndex

I figure I have to assign the data to a value or something along those lines but I can't wrap my noggin around it at the moment.

 

[SkipVought]

Hi,

Hide the columns you don't want to copy.

Select ALL cells.

COPY

Select the other sheet A1.

Edit > Paste Special -- VALUES.

ie you don't need VBA to loop thru rows!

If you want to use VBA to repeat this, turn on your macro recorder.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rustychef]

Just an idea, but it might be easier to continue using the code you have and then set up a few more lines to delete the columns you dont want to keep.

 

[alazar]

Worksheet("Products") is a master product list that contains multiple families of products.

The macro searches the first column of "Products" for a family match based on the initial 6 or 7 characters of the string and then copies certain columns based on a drop down.

I need this to be automated via vba to save time since the master product will change/increase over time.

 

[SkipVought]

The macro searches the first column of "Products" for a family match based on the initial 6 or 7 characters

Use AutoFilter BEGINS WITH.

COPY the visible cells

Paste Special -- VALUES

3 simple steps.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

In fact, if I were doing it, I'd use MS Query to grab each resultset.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alazar]

If I knew more about VBA then I'd know what you're talking about for MS Query.

Anyways, I went about it another way and have it more or less doing what I need it to do but it seems to copy over the last row twice to the new sheet.

Here's the code I have rewritten (forgive me I'm not a programmer and learn through trial and error):

For RowIndex = 1 to 250
If Left(curCell, 6) = ProductFamily Then
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 1).Value = Worksheets("Products").Cells(RowIndex, 1)
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 2).Value = Worksheets("Products").Cells(RowIndex, 2)
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 3).Value = Worksheets("Products").Cells(RowIndex, 4)
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 4).Value = Worksheets("Products").Cells(RowIndex, 12)
Worksheets("PriceListTemplate").Range("A" & 5 + CPLIndex, "D" & 5 + CPLIndex).Select
Range("A" & 5 + CPLIndex, "D" & 5 + CPLIndex).Activate
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Cells.Select
CPLIndex = CPLIndex + 1
End If
Next RowIndex

CPLIndex is assigned a value of 1.

Would be appreciated if you gurus know what I'm doing wrong here (besides poor programming) or why the code causes the last search row match to duplicate/copy onto the other sheet?

 

[SkipVought]

STEP (F5) thru your code and discover what is happening. Use the Watch Window to "See" the values, and properties of your variables and objects. faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rustychef]

You might have to explain some more on what your code is trying to do.


For RowIndex = 1 to 250
If Left(curCell, 6) = ProductFamily Then
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 1).Value = Worksheets("Products").Cells(RowIndex, 1)
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 2).Value = Worksheets("Products").Cells(RowIndex, 2)
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 3).Value = Worksheets("Products").Cells(RowIndex, 4)
Worksheets("PriceListTemplate").Cells(5 + CPLIndex, 4).Value = Worksheets("Products").Cells(RowIndex, 12)

I think Im following you up to here. Here's how Im interpreting it: For example lets say the very first row finds a match. Then, if the current cell on ws "Products" contains the the string contained in the variable "ProductFamily", then copy the current row values of columns A, B, D, & L to the cell range A6-D6 on ws "PriceListTemplate". Right so far?

Now it gets fuzzy...

Select A6-D6 from "PriceListTemplate" (This cant be done without first Activating the sheet)
Worksheets("PriceListTemplate").Range("A" & 5 + CPLIndex, "D" & 5 + CPLIndex).Select


WHO are you trying to activate A6-D6? Because right now your current sheet is still "Products"
Range("A" & 5 + CPLIndex, "D" & 5 + CPLIndex).Activate

WHERE are you intending to copy from? "Products" or "PriceListTemplate". Remember, your current sheet is still "Products"
Selection.Copy

WHERE is this taking place? Because right now, you have shifted the cells A6-D6 in "Products" down one line and have disassociated columns A-D from the rest of the data on your entire "Products" ws
Selection.Insert Shift:=xlDown


Your current cell should now be A2 on ws "Products"
ActiveCell.Offset(1, 0).Cells.Select
CPLIndex = CPLIndex + 1
End If
Next RowIndex

If you could clear up some of this I might be able to help. It almost sounds like you are trying to use 3 ws not 2.

 

[alazar]

@rustychef

As I mentioned to SkipVought, I'm not a programmer and have very little knowledge in vba programming. The code that I pieced together thus far are from various snippets of code I found on the web.

It's doing what I need, it may not be the cleanest but my supervisor just wants the end result.

If it finds a match it copies the respective cells/columns in Products to PriceListTemplate. Then it moves to the next row in PriceListTemplate until it can no longer find a matching ProductFamily. If I don't shift it down one row, it overwrites the contents.

 

[SkipVought]

You made a statement. Is there a question in there? If so, we need SPECIFICS, like the vba, the workbook structure, the data, the results, etc.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rustychef]

alazar,

First off, if I seemed like I was picking on your code in my last post, I wasn't. I just need more info. Everything my seem simple to you because you have the workbook in front of you, be we don't have that luxury... so we ask for more specifics. If a customer wants a birthday cake you might want to ask the recipients name and what kind they like.

Secondly, I still need a little information. What you gave helped a little, but based on your code examples thats not what you're attempting to do.

The issue with your worksheet having 2 duplicate rows at the end lies in these 2 lines:

Selection.Copy
Selection.Insert Shift:=xlDown

The first line copies A-D, then without moving to a new worksheet, you insert the same data. Thats the source of the double data. Keep in mind that if you have more than 4 columns of data, you are destroying your data integrity!! Every time you insert the data it will orphan the rest of the columns from their original 4 columns.

At this point, the best way I can think of doing this is for you to make a macro of exactly what it is you're trying to do. instead of relying on the Left() function for comparison, and looping thru the entire workbook, just "eyeball" it and/or pretend that it matches, do the manualy cut/copy/paste, insert columns/rows, formatting, from one sheet to another, save the macro, then paste the contents of the macro so we can take a look at what you're trying to accomplish.

If you want help, thats what I need. What you're saying doesn't match up with the code you're posting. For example in your code you're copying over columns A,B,D and L. But then for some reason after that is done you're copying cells A-D of row "x" to somewhere?!?! You're also using an "insert cells" operation (but only for A-D).

You never address these parts of your code, but it seems to be critical

Im willing to help, but dont tie my hands.

P.S. I already have a "shell" for your code, but need to see what the other lines are about

 

[alazar]

Thanks for the help and insight.

Just gotta ironout the quirks.