If a certain value then paste in a different range?

[daseffects]

Sorry if this post comes up twice.

I'm almost there with the below - what I ideally need is that for each increase in the value of ("c3&quot that the data is pasted some number of columns over from the previous - I could type it all out but there must be an easier way

eg if value = 1 paste in E5, If 2 paste in H5, If 3 past in K5.



Sub copy_data()

Application.ScreenUpdating = False
Sheets("sheet1&quot.Range("E6:F7&quot.Select
Selection.Copy
If Range("C3&quot = 1 Then
Sheets("Sheet2&quot.Select
Range("E5&quot.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Else
Sheets("Sheet2&quot.Select
Range("H5&quot.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Sheets("sheet1&quot.Select
Application.ScreenUpdating = True

End Sub

Thanks

 

[longhair]

daseffects,

is there any logic in how many columns to move over? for example for each increase of 1 in value the column is moved over 2?
also, might be easier to use case rather then if...then...elseif...etc

regards,
longhair

 

[daseffects]

I'm just testing the concept with a small range at this point. Ideally the code pastes the data 1 colum away from the previous data based on the widthe of the range.

Though for my needs a fixed number would work.

Thanks for any help on this.

 

[daseffects]

Strike that - I actually need the results to be pasted a fixed number away based on the selected value -

1 = h5
2 = H10 or 2nd position
3 = h15 or 3rd position

Where I have the facilty to change the increment based on the width of the range either through code or automaticaly.

Thanks

 

[SkipVought]

Hi,

How 'bout this...
code]
Sub copy_data()

Application.ScreenUpdating = False
Sheets("sheet1&quot.Range("E6:F7&quot.Copy
Sheets("Sheet2&quot.Cells(5, [C3] * 3).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
[/code]


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[daseffects]

SkipVought,

Good stuff, just what I need.

I need to start streamlining my macros based on recording Macros as there's all that extra code.

Thanks Again.

D

 

[SkipVought]

I would encourage you to try to code with a minimum of Activates and Selects of Objects. Activating and Selecting Objects, slows processing down.

When you are going to refer to an object or object property more than once, use the With object...End With construct. An example might be...

Sub copy_data()

    Application.ScreenUpdating = False
    With Sheets("sheet1").Range("E6:F7")
      CellCount = .Cells.Count 
      .Copy
    End With
    With Sheets("Sheet2").Cells(5, [C3] * 3)
       .PasteSpecial (xlPasteValues)
       .CurrentRegion.Interior.Color = vbGreen
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Here, in addition to copying the source range, you get the cell count in a variable - and
the paste destination range is shaded with standard green.

Another thing to do is set object variables. For instance, if your procedure had to go back and forth between workbook1 sheet1 and workbook2 sheet1...

Dim wsOld as Worksheet, wsNew as Worksheet, rngO as Range
Set wsOld = Activesheet
Workbooks.Add
Set wsNew = Activesheet
For Each rngO in wsOld.UsedRange
   With rngO
      wsNew.Cells(.Row, .Column).Value = .Value
   End With
Next
Set wsOld = Nothing
Set wsNew = Nothing

would write each value from the old sheet to the new sheet in the new workbook.

Ain't codin' fun!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com