Hi Guys,

Can anyone help me with making a macro to copy and paste a cell.


Here’s the sample data

A B C D
1 Code1 Tom Jones
2 deny
3 credit
4 Code2 Jim Tyles
5 good
6 Code3 Sam King
7 bad credit

I need the macro to look into column A and look for blank cells. I
there’s a blank cell, it needs to copy the row starting with A to K an
paste it column L or in the row before that blank cell and if there
another blank cell after the first one, it needs to copy and paste i
column W and then loops again till it finds another blank cell.

It needs to look like this:

A B C D L W
1 Code1 Tom Jones deny credit
2 deny
3 credit
4 Code2 Jim Tyles good
5 good
6 Code3 Sam King bad credit
7 bad credit

Hope you can help guys, this is just part of what I need to do with
raw data. I just made a macro to reformat the sheet. And, this kin
of macro is too complex for me :(

Thanks in advanc

--
japorm
-----------------------------------------------------------------------
japorms's Profile: http://www.excelforum.com/member.php...nfo&userid=654
View this thread: http://www.excelforum.com/showthread.php?threadid=56246

Have a look at http://www.rondebruin.nl/tips.htm :

--
s80N

-----------------------------------------------------------------------
s80NL's Profile: http://www.excelforum.com/member.php...fo&userid=3637
View this thread: http://www.excelforum.com/showthread.php?threadid=56246

Thanks for the reply but it doesnt work.

To make it short, the desription is in always after the first row if
ever there is a description and the desription always start in column
B.

Hi Guys,

It's me again. I have this macro and I just need to edit to my
requirements.

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
..Calculation = xlCalculationManual
..ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
..DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf .Cells(Lrow, "A").Value = 0 Then .Rows(Lrow)._Copy_
'This will delete each row with the Value "ron" in
Column A, case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
..ScreenUpdating = True
..Calculation = CalcMode
End With

End Sub

I only need it to copy the blank rows and paste it to rows before it

the blank row is A2 (but the B2 until K has contents) and the non blank
row is A1. I need the macro to copy and paste the row A2 to K2 to the
row before it which is A1 but it needs to be paste to L1.

Can anybody help? please.....


Graham Whitehead Wrote:
Hmm, not too sure about you description of things - however, this does
the
job (see attached sheet).

Here is the code:

Sub Test()

Dim x As Integer
Dim strEval As String

For x = 0 To 7

strEval = Range("B1").Offset(x, 0).Value

If strEval = "Deny" Then
Range("E1").Offset(x - 1, 0).Value = "Deny"
Range("F1").Offset(x - 1, 0).Value = "Credit"
ElseIf strEval = "Good" Then
Range("E1").Offset(x - 1, 0).Value = strEval
ElseIf strEval = "Bad Credit" Then
Range("E1").Offset(x - 1, 0).Value = "Bad"
Range("F1").Offset(x - 1, 0).Value = "Credit"
End If

Next x

End Sub



"japorms" wrote
in
message ...

Hi Guys,

Can anyone help me with making a macro to copy and paste a cell.


Here's the sample data

A B C D
1 Code1 Tom Jones
2 deny
3 credit
4 Code2 Jim Tyles
5 good
6 Code3 Sam King
7 bad credit

I need the macro to look into column A and look for blank cells. If
there's a blank cell, it needs to copy the row starting with A to K
and
paste it column L or in the row before that blank cell and if theres
another blank cell after the first one, it needs to copy and paste
it
column W and then loops again till it finds another blank cell.

It needs to look like this:

A B C D L W
1 Code1 Tom Jones deny credit
2 deny
3 credit
4 Code2 Jim Tyles good
5 good
6 Code3 Sam King bad credit
7 bad credit

Hope you can help guys, this is just part of what I need to do with
a
raw data. I just made a macro to reformat the sheet. And, this
kind
of macro is too complex for me :(

Thanks in advance


--
japorms

------------------------------------------------------------------------
japorms's Profile:
http://www.excelforum.com/member.php...fo&userid=6544
View this thread:
http://www.excelforum.com/showthread...hreadid=562466



--
japorms
------------------------------------------------------------------------
japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
View this thread: http://www.excelforum.com/showthread...hreadid=562466