Copy Paste Down Macro
 

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?

Copy Paste Down Macro
 

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

---
Per

"MCheru" skrev i meddelelsen
...
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?

Copy Paste Down Macro
 

This macro is awesome. Thank you for you're help. It works great!

"Per Jessen" wrote:

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

---
Per

"MCheru" skrev i meddelelsen
...
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?

Copy Paste Down Macro
 

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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

thanks Per,
it works.

"Per Jessen" wrote:

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

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

"massi" wrote:

thanks Per,
it works.

"Per Jessen" wrote:

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

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.

--
Per


"massi" skrev i meddelelsen
...
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

"massi" wrote:

thanks Per,
it works.

"Per Jessen" wrote:

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

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

"Per Jessen" wrote:

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.

--
Per


"massi" skrev i meddelelsen
...
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

"massi" wrote:

thanks Per,
it works.

"Per Jessen" wrote:

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

Hello

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

Per

"massi" skrev i meddelelsen
...
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

"Per Jessen" wrote:

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.

--
Per


"massi" skrev i meddelelsen
...
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

"massi" wrote:

thanks Per,
it works.

"Per Jessen" wrote:

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

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



"Per Jessen" wrote:

Hello

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

Per

"massi" skrev i meddelelsen
...
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

"Per Jessen" wrote:

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.

--
Per


"massi" skrev i meddelelsen
...
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

"massi" wrote:

thanks Per,
it works.

"Per Jessen" wrote:

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

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

"massi" skrev i meddelelsen
...
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



"Per Jessen" wrote:

Hello

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

Per

"massi" skrev i meddelelsen
...
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

"Per Jessen" wrote:

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.

--
Per


"massi" skrev i meddelelsen
...
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

"massi" wrote:

thanks Per,
it works.

"Per Jessen" wrote:

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

"massi" skrev i meddelelsen
...
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

"MCheru" wrote:

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?

Copy Paste Down Macro
 

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


*** Sent via Developersdex http://www.developersdex.com ***