| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 *** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
| copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
| Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| Macro to Copy/Paste then Paste to Next Line | Excel Programming | |||
Linear Mode
