|
Yet another copy/paste macro question
My apologies in advance if this is redundant. Didn't find what I was looking
for via search. I have a client info spreadsheet that i'm looking to automate a bit more. I put in a button and tied it to a macro that copies the line the cursor is on, inserts a row below where the cursor is, and erases all client data (save for the cells with formulas in them) to create a blank row with formulas for a new client. This is tedious and doesn't work too well when creating client data in a new section of the spreadsheet (where this is no source row to copy from). Here's what I have: ows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 2).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -15).Range("A1").Select ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 What I would like.. is to eliminate all those rose of "select, clear contents" and just have it copy the row from "1:1:" where I have a blank client template. so.. it would work like this, the cursor is set to the row where the new line will be put in (the line will be added to the row BELOW where the cursor is set), it copies row 1:1, and pastes it into the newly created blank row. Thanks in advance! |
Yet another copy/paste macro question
Try this
Sub copy() Rows("1:1").EntireRow.copy _ Destination:=ActiveCell End Sub Active cell has to be in Col A for this to work... "Btate0121" wrote: My apologies in advance if this is redundant. Didn't find what I was looking for via search. I have a client info spreadsheet that i'm looking to automate a bit more. I put in a button and tied it to a macro that copies the line the cursor is on, inserts a row below where the cursor is, and erases all client data (save for the cells with formulas in them) to create a blank row with formulas for a new client. This is tedious and doesn't work too well when creating client data in a new section of the spreadsheet (where this is no source row to copy from). Here's what I have: ows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 2).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -15).Range("A1").Select ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 What I would like.. is to eliminate all those rose of "select, clear contents" and just have it copy the row from "1:1:" where I have a blank client template. so.. it would work like this, the cursor is set to the row where the new line will be put in (the line will be added to the row BELOW where the cursor is set), it copies row 1:1, and pastes it into the newly created blank row. Thanks in advance! |
Yet another copy/paste macro question
|
Yet another copy/paste macro question
This seems to do the job... except it keeps hiding the added row.
Also, I forgot to mention the worksheet is protected. So I'll need the "protect. Password="" " function. Whe I added it manually, it seems to break the code. any help there? "Sheeloo" wrote: Try this Sub copy() Rows("1:1").EntireRow.copy _ Destination:=ActiveCell End Sub Active cell has to be in Col A for this to work... "Btate0121" wrote: My apologies in advance if this is redundant. Didn't find what I was looking for via search. I have a client info spreadsheet that i'm looking to automate a bit more. I put in a button and tied it to a macro that copies the line the cursor is on, inserts a row below where the cursor is, and erases all client data (save for the cells with formulas in them) to create a blank row with formulas for a new client. This is tedious and doesn't work too well when creating client data in a new section of the spreadsheet (where this is no source row to copy from). Here's what I have: ows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 2).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -15).Range("A1").Select ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 What I would like.. is to eliminate all those rose of "select, clear contents" and just have it copy the row from "1:1:" where I have a blank client template. so.. it would work like this, the cursor is set to the row where the new line will be put in (the line will be added to the row BELOW where the cursor is set), it copies row 1:1, and pastes it into the newly created blank row. Thanks in advance! |
Yet another copy/paste macro question
figured out the hidden row. I need to hide row 1... which is why the copied
row hides (because the source row is hidden). So I just need to know how to unlock it, copy/paste the hidden row, and finally re-lock the sheet. "Btate0121" wrote: This seems to do the job... except it keeps hiding the added row. Also, I forgot to mention the worksheet is protected. So I'll need the "protect. Password="" " function. Whe I added it manually, it seems to break the code. any help there? "Sheeloo" wrote: Try this Sub copy() Rows("1:1").EntireRow.copy _ Destination:=ActiveCell End Sub Active cell has to be in Col A for this to work... "Btate0121" wrote: My apologies in advance if this is redundant. Didn't find what I was looking for via search. I have a client info spreadsheet that i'm looking to automate a bit more. I put in a button and tied it to a macro that copies the line the cursor is on, inserts a row below where the cursor is, and erases all client data (save for the cells with formulas in them) to create a blank row with formulas for a new client. This is tedious and doesn't work too well when creating client data in a new section of the spreadsheet (where this is no source row to copy from). Here's what I have: ows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 2).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -15).Range("A1").Select ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 What I would like.. is to eliminate all those rose of "select, clear contents" and just have it copy the row from "1:1:" where I have a blank client template. so.. it would work like this, the cursor is set to the row where the new line will be put in (the line will be added to the row BELOW where the cursor is set), it copies row 1:1, and pastes it into the newly created blank row. Thanks in advance! |
Yet another copy/paste macro question
Try
Sub copy() ActiveSheet.Unprotect Password:="abc123" Rows("1:1").Hidden = False rng = "A" & Right(ActiveCell.Address, 2) Range(rng).Select Rows("1:1").EntireRow.copy _ Destination:=ActiveCell Rows("1:1").Hidden = True ActiveSheet.Protect Password:="abc123" End Sub "Btate0121" wrote: figured out the hidden row. I need to hide row 1... which is why the copied row hides (because the source row is hidden). So I just need to know how to unlock it, copy/paste the hidden row, and finally re-lock the sheet. "Btate0121" wrote: This seems to do the job... except it keeps hiding the added row. Also, I forgot to mention the worksheet is protected. So I'll need the "protect. Password="" " function. Whe I added it manually, it seems to break the code. any help there? "Sheeloo" wrote: Try this Sub copy() Rows("1:1").EntireRow.copy _ Destination:=ActiveCell End Sub Active cell has to be in Col A for this to work... "Btate0121" wrote: My apologies in advance if this is redundant. Didn't find what I was looking for via search. I have a client info spreadsheet that i'm looking to automate a bit more. I put in a button and tied it to a macro that copies the line the cursor is on, inserts a row below where the cursor is, and erases all client data (save for the cells with formulas in them) to create a blank row with formulas for a new client. This is tedious and doesn't work too well when creating client data in a new section of the spreadsheet (where this is no source row to copy from). Here's what I have: ows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 2).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, 1).Range("A1").Select Selection.ClearContents ActiveCell.Offset(0, -15).Range("A1").Select ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 What I would like.. is to eliminate all those rose of "select, clear contents" and just have it copy the row from "1:1:" where I have a blank client template. so.. it would work like this, the cursor is set to the row where the new line will be put in (the line will be added to the row BELOW where the cursor is set), it copies row 1:1, and pastes it into the newly created blank row. Thanks in advance! |
Yet another copy/paste macro question
|
| All times are GMT +1. The time now is 04:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com