Trying to simplify my macro code...

[Zurina]

Hi experts out there.
Help me plssss...

I need to simplify the codes below because i know if I still continue with these, the procedure are going to be large.

The problem is I just dun know how to put the cut, copy and paste things into formula.

Appreciate your help.
Thanks. Zurina

KL, Malaysia.





Sub UIC()
'
' UIC Macro
'
' Keyboard Shortcut: Ctrl+o


Select Case Sheets("Fs").Range("A9").Value

'A-MYR

Case 1
Sheets("UIC").Select
Range("C10:L10").Select
Selection.Copy
Range("C11").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("C10:L10").Select
Selection.Copy
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Case 2
Sheets("UIC").Select
Range("C11:L11").Select
Selection.Copy
Range("C12").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("C11:L11").Select
Selection.Copy
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Case 3
Sheets("UIC").Select
Range("C12:L12").Select
Selection.Copy
Range("C13").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("C12:L12").Select
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[njimack]

Based on your existing code, the below should work (note: untested)

Sub UIC()
Dim x As Long
Application.ScreenUpdating = False
x = Sheets("Fs").Range("A9")

With Sheets("UIC")
    With .Cells(9 + x, 3).Resize(1, 10)
        .Copy .Offset(1)
        .Formula = .Value
    End With
End With
Application.ScreenUpdating = True

End Sub

 

[Michael M]

Change your cases to something like this

Case 1
Sheets("UIC").Range("C10:L10").Copy Destination:=Range("C11")
With Sheets("UIC").Range("C10:L10")
    .Value = .Value
End With

 

[Zurina]

Ok..i 'm lost but i'll try..thanks anyway njimack..

 

[bewsh1987]

ADVERTISEMENT

Change your cases to something like this

Case 1
Sheets("UIC").Range("C10:L10").Copy Destination:=Range("C11")
With Sheets("UIC").Range("C10:L10")
    .Value = .Value
End With

try the below

Sub UIC()
Value = Mid(Sheets("Fs").Range("A9").Value, 6, 1) + 9
Valu2 = "C" & Value & ":L" & Value
Value3 = "C" & Value + 1
Sheets("UIC").Range(Valu2).Copy Destination:=Range(Value3)
With Sheets("UIC").Range(Valu2)
    .Value = .Value
End With

End Sub

 

[njimack]

Ok..i 'm lost but i'll try..thanks anyway njimack..

Given the code you posted, you don't need to use Select Case. The value you're testing is an integer (1,2,3) and at each change in this, you're incrementing the row you're copying/pasting by 1.

 

[Zurina]

Change your cases to something like this

Case 1
Sheets("UIC").Range("C10:L10").Copy Destination:=Range("C11")
With Sheets("UIC").Range("C10:L10")
    .Value = .Value
End With

Michael, i have Case 1 - 12, should i put the
n = Sheets("Fs"). Range ("A9").Value
Sheets("UIC").Range("C:L" & n + 10)..blabla