PDA

View Full Version : I Need Vba Code Copy Of Cell And Paste To Other Cell



etheer
12-21-2013, 02:01 AM
Hi

I Need Vba Code Copy Of Cell And Paste To Other Cell
10994
copy cell A and paste in cell E

copy cell B and paste in cell F

copy cell C and paste in cell G

copy cell D and paste in cell H

if there are numbers in cells E , F , G , H

copy and paste in empty cells I , J , K , L

ashleyuk1984
12-21-2013, 05:03 AM
1. Is your data always in columns of four?? ie ABCD & EFGH etc etc, or could it be AB & CD another day ??
2. And will the data stop at I J K L.... or will the same apply for M N O P ??

Thanks

Trebor76
12-21-2013, 05:07 AM
Hi etheer,

Try this:


Option ExplicitSub Macro1()


Application.ScreenUpdating = False


Dim rngCell As Range

With Range("E1:H8")
.Formula = "=A1"
.Value = .Value
End With

For Each rngCell In Range("E1:H8")
If IsNumeric(rngCell) = True Then
rngCell.Offset(0, 4).Value = Val(rngCell)
End If
Next rngCell

Application.ScreenUpdating = True


End Sub

Regards,

Robert

etheer
12-21-2013, 10:51 AM
Thank You Trebor76

The problem repeating paste column
10996

I need copy cells in colums A , B , C , D

and paste in first empty cells after the cell there are number

very time run macro

etheer
12-21-2013, 03:04 PM
please help me

Aussiebear
12-21-2013, 04:01 PM
It appears to me that rather Copy and Paste, you are simply better off inserting 4 spaces ("A: D")

etheer
12-21-2013, 05:06 PM
I get this code and work


Sub oddinho2z()
x = 5
Range("A2:A8,B2:B8,C2:C8,D2:D8").Copy
Range("E2").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(, 1).Select
x = x - 10
If x = 0 Then Exit Sub
Loop
ActiveCell.Select
ActiveSheet.Paste
End Sub


I hope repaired this code if there is error

Trebor76
12-21-2013, 07:43 PM
If you could provide before and after screen shots of what you're needing it may make providing a solution easier.

Aussiebear
12-21-2013, 11:16 PM
Try the followingSub ShiftRight()
With Range("A1:D8")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
End Sub


Since you are essentially copying A1:D8 to E1:H8 providing there are no data in cells E1:H8, however if there is data in E1:H8 then copy E1:H8 to I1:L8, which can be simply overcome with one move by inserting the group of cells in A1:D8 and push everything to the right.

Trebor76
12-22-2013, 02:13 AM
Oh OK I think I get it now (thanks Aussiebear). If you're trying to always copy A1:E8 to next available section, the following will do the job:


Option ExplicitSub Macro1()


Dim lngMyCol As Long

lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

With Range(Cells(2, lngMyCol + 1), Cells(8, lngMyCol + 4))
.Formula = "=A2"
.Value = .Value
End With


End Sub

Aussiebear - do you think the blues will be able to do it in 2014?

Robert

Aussiebear
12-22-2013, 11:11 PM
@Robert. They are getting closer each year

SamT
12-26-2013, 11:52 AM
Sub SamT()
Dim LastRow As Long
Dim LastCol As Long

LastRow = Range("A1").CurrentRegion.Rows.Count
LastCol = Range("A1").CurrentRegion.Columns.Count

Range(Range("A1"), Cells(LastRow, 4)).Copy
Cells(1, LastCol + 1).PasteSpecial (xlPasteValues)

End Sub