Macro for copying and duplicating data based on a cell values.

**outthere** · 08-22-2009, 05:25 PM

I am trying to copy two cells from one worksheet to another in the same work book based on the value in Column B of one of the sheets. This is just a building block to a larger script I am going to create.

Below is my attempt but I keep getting an error at Range(Cells(x, 3)).Select.

Any help would be great.

Sub Macro3()
'
' Macro3 Macro
'

x = 2
i = 1

Do While i > 0
    Worksheets("Macro List").Activate
    Rows("3:3").Select
    Range("B3").Activate
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B3").Select
    Worksheets("Gary Breakout").Activate
    Sheets("Gary Breakout").Select
    Range(Cells(x, 3)).Select
    Selection.Copy
    Worksheets("Macro List").Activate
    Sheets("Macro List").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Worksheets("Gary Breakout").Activate
    Sheets("Gary Breakout").Select
    Range(Cells(x, 4)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Worksheets("Macro List").Activate
    Sheets("Macro List").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D3").Select
    
    i = Cells(x, 2)
        x = x + 1
    Loop
    
End Sub

**shg** · 08-22-2009, 05:56 PM

Maybe like this:

Sub Macro3()
    Dim x As Long
    Dim i As Long
    
    x = 2
    i = 1

    Do While i > 0
        With Worksheets("Macro List")
            .Rows(3).Insert
            .Range("B3").Value = Worksheets("Gary Breakout").Cells(x, 3).Value
            .Range("C3").Value = Worksheets("Gary Breakout").Cells(x, 4).Value
            i = .Cells(x, 2)
        End With
        x = x + 1
    Loop
End Sub

**outthere** · 08-25-2009, 10:22 AM

I tried the code and i get a "Type Mismatch" Error at line:

i = .Cells(x, 2)

Any ideas?

By the way, thanks for the help, I have a lot of learning to do!

**outthere** · 08-25-2009, 10:27 AM

I forgot to mention I am using Excel 2007 on Vista 64 Business, if that matters.

**shg** · 08-25-2009, 10:32 AM

I tried the code and i get a "Type Mismatch" Error at line:

i = .Cells(x, 2)

Chances are the cell contain a non-numeric value.

**outthere** · 08-25-2009, 10:45 AM

You are correct. It contains a count formula, any chance of making it work around that or will i have to paste values in the cell?

**outthere** · 08-25-2009, 11:09 AM

Ok i fixed it, the reference cell for "i" was in the other worksheet:

Sub Macro4()
    Dim x As Long
    Dim i As Long
    
    x = 1
    i = 1

    Do While i > 0
        x = x + 1
        With Worksheets("Macro List")
            .Rows(3).Insert
            .Range("B3").Value = Worksheets("Gary Breakout").Cells(x, 3).Value
            .Range("C3").Value = Worksheets("Gary Breakout").Cells(x, 4).Value
            i = Worksheets("Gary Breakout").Cells(x, 2).Value
        End With
    Loop
End Sub

**outthere** · 08-25-2009, 12:31 PM

Thanks for the help. I have posted a new issue in a new thread found here:

http://www.excelforum.com/excel-prog...ml#post2152004

I guess it's a bit of a continuation of this one but any help would be appreciated.