Copy Paste Down Macro


M

MCheru

I am trying to create a macro that will search A3:G100 for blank rows. When
a blank row is found, I want the macro to copy the contents in the cell above
it A(blank):G (blank) and paste those contents in each blank row going down
((A(blank):G (blank)) until the next row with contents is reached. Is that
possible?
 
Ad

Advertisements

P

Per Jessen

This should do it:

Sub FillBlankRows()
Dim BlankCell As Integer
Dim r As Long
Dim col As Long

For r = 3 To 100
For col = 1 To 7
If Cells(r, col).Value = "" Then
BlankCell = BlankCell + 1
End If
Next
If BlankCell = 7 Then
Range("A" & r - 1 & ":G" & r - 1).Copy Range("A" & r)
End If
BlankCell = 0
Next
End Sub

Hopes it helps
 
M

massi

Hi there,
i have a similar request so you might able to help...

i have a spread sheet where i have to record some events.
the first row is already formatted and the first cell has a number 1.
i would like a macro to automatically create another line each time I press
a button and that would increase the number of the first cell from the
previous line.

i have found this one:

Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Rows(Lastrow).Copy
Rows(Lastrow).Insert
Rows(Lastrow + 1).SpecialCells(xlCellTypeConstants).ClearContents

but it only add the row without increasing the cell in column a

hope you can help.
thanks
 
P

Per Jessen

Hello

Try this:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(LastRow).Copy
Rows(LastRow).Insert
Rows(LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents
Cells(LastRow + 1, 1) = Cells(LastRow, 1) + 1

Regards,
Per
 
M

massi

thanks Per,
it works.

Per Jessen said:
Hello

Try this:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(LastRow).Copy
Rows(LastRow).Insert
Rows(LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents
Cells(LastRow + 1, 1) = Cells(LastRow, 1) + 1

Regards,
Per
 
Ad

Advertisements

M

massi

here i am again..

there was something i haven't thought; the sheet will be protected and when
running the macro an error appears (run-time error'1004')

is there a way to make the macro work even if te heet is protected?

thanks
 
P

Per Jessen

Hi again

You have to unprotect the sheet by macro the do your stuff and protect the
sheet again:

Sheets("Sheet1").UnProtect Password:="JustMe"
'Your code
Sheets("Sheet1").Protect Password:="JustMe"

Hopest this helps.
 
M

massi

hello,
it doesn't work.
i get an error message...

what am i doing wrong? when i set up the password i also tick the option for
the user to inser rows..

cheers
 
P

Per Jessen

Hello

Post your entire code, state the the error message and let me know which
line is highlighted when you click debug.

Per
 
M

massi

hello,

here it is:

Private Sub CommandButton2_Click()


'
' add_item Macro
' Macro recorded 25/03/2009 by IT Department
'
Sheets("ShutdownEvents").Unprotect Password:="qaz"
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(LastRow).Copy
Rows(LastRow).Insert
Rows(LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents
Cells(LastRow + 1, 1) = Cells(LastRow, 1) + 1
Sheets("ShutdownEvents").Protect Password:="qaz"
End Sub

and the error msg is:
Run time error 1004
insert method of range class failed

and it doesn't give me the option to debug but only to end

rgds
 
Ad

Advertisements

P

Per Jessen

Hello,
Insert a breakpoint after the line LastRow = ...., and run the macro. What
is the value of LastRow (place the mousepointer over the variable to see it)
?

Then try to qualify the sheet as you calculate LastRow:

LastRow = Sheets("ShutdownEvents").Range("A" & Rows.Count).End(xlUp).Row

Does it make any difference?

Hopes this helps.

-Per
 
Ad

Advertisements

P

piyush mangla

Hello Per

I just want to ask one macro for my problem.....
I want to copy all the cells of A column the number of times value in B
column.
For eg
If A1 is = 'abc' and B1 is = 12 then i would like to copy A1 '12' times
beneath it ie from A1 to A12.

Please Help to sort out
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top