Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using marcos to copy large amounts of info
I would like to write a macro to copy information from one cell to several
others and then copy other info from one cell to several other cells |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using marcos to copy large amounts of info
Try this to copy the A1 value in B1:G1000
Range("B1:G1000").Value = Range("A1").Value Or with two areas Range("B1:G1000,I1:I5").Value = Range("A1").Value -- Regards Ron de Bruin http://www.rondebruin.nl "mja" wrote in message ... I would like to write a macro to copy information from one cell to several others and then copy other info from one cell to several other cells |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using marcos to copy large amounts of info
My spread sheet is over 33000 row long, I have an employee name in a1 and
need that same name copied into cells a2-a9 and then an other employee name a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish so that I can use a filer to extract certain information but keep it attahced to teh right employee. Thanks "mja" wrote: I would like to write a macro to copy information from one cell to several others and then copy other info from one cell to several other cells |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using marcos to copy large amounts of info
This macro to fill blank cells in chosen Column will do the trick if I read this
right. Sub Fill_Blanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above Dim wks As Worksheet Dim Rng As Range Dim LastRow As Long Dim col As Long Set wks = ActiveSheet With wks col = ActiveCell.Column 'or 'col = .range("b1").column Set Rng = .UsedRange 'try to reset the lastcell LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set Rng = Nothing On Error Resume Next Set Rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Rng Is Nothing Then MsgBox "No blanks found" Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If 'replace formulas with values With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub You could also do it manually.......... Select Column A and F5SpecialBlanksOK Enter an = sign in active cell then point to cell above and hit CTRL + ENTER. Copy Column A and Paste SpecialValuesOKEsc. Gord Dibben MS Excel MVP On Tue, 25 Jul 2006 11:08:02 -0700, mja wrote: My spread sheet is over 33000 row long, I have an employee name in a1 and need that same name copied into cells a2-a9 and then an other employee name a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish so that I can use a filer to extract certain information but keep it attahced to teh right employee. Thanks "mja" wrote: I would like to write a macro to copy information from one cell to several others and then copy other info from one cell to several other cells |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
using marcos to copy large amounts of info
Thank you for your response.
I have tried this and I'm sure there is just one little thing that I do not have right but when I try to run teh macro I get and error message that says "invalid outside procedure" and it highlights where it is "Set wks = ActiveSheet" I have typed the macro as you sent it but I must not have it right. Thanks again "Gord Dibben" wrote: This macro to fill blank cells in chosen Column will do the trick if I read this right. Sub Fill_Blanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above Dim wks As Worksheet Dim Rng As Range Dim LastRow As Long Dim col As Long Set wks = ActiveSheet With wks col = ActiveCell.Column 'or 'col = .range("b1").column Set Rng = .UsedRange 'try to reset the lastcell LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set Rng = Nothing On Error Resume Next Set Rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Rng Is Nothing Then MsgBox "No blanks found" Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If 'replace formulas with values With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub You could also do it manually.......... Select Column A and F5SpecialBlanksOK Enter an = sign in active cell then point to cell above and hit CTRL + ENTER. Copy Column A and Paste SpecialValuesOKEsc. Gord Dibben MS Excel MVP On Tue, 25 Jul 2006 11:08:02 -0700, mja wrote: My spread sheet is over 33000 row long, I have an employee name in a1 and need that same name copied into cells a2-a9 and then an other employee name a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish so that I can use a filer to extract certain information but keep it attahced to teh right employee. Thanks "mja" wrote: I would like to write a macro to copy information from one cell to several others and then copy other info from one cell to several other cells |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
using marcos to copy large amounts of info
Do not type the code into a module.....copy it from the post and paste it in.
With workbook open hit ALT + F11 to get to Visual Basic Editor. CTRL + r to open Project Explorer. Select your workbook/project and right-clickInsertModule. Paste the code in there. Add the line Option Explicit above the top line. ALT + q to return to Excel window. F8 to run the macro on the activecell column. Gord On Fri, 4 Aug 2006 14:05:02 -0700, mja wrote: Thank you for your response. I have tried this and I'm sure there is just one little thing that I do not have right but when I try to run teh macro I get and error message that says "invalid outside procedure" and it highlights where it is "Set wks = ActiveSheet" I have typed the macro as you sent it but I must not have it right. Thanks again "Gord Dibben" wrote: This macro to fill blank cells in chosen Column will do the trick if I read this right. Sub Fill_Blanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above Dim wks As Worksheet Dim Rng As Range Dim LastRow As Long Dim col As Long Set wks = ActiveSheet With wks col = ActiveCell.Column 'or 'col = .range("b1").column Set Rng = .UsedRange 'try to reset the lastcell LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set Rng = Nothing On Error Resume Next Set Rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Rng Is Nothing Then MsgBox "No blanks found" Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If 'replace formulas with values With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub You could also do it manually.......... Select Column A and F5SpecialBlanksOK Enter an = sign in active cell then point to cell above and hit CTRL + ENTER. Copy Column A and Paste SpecialValuesOKEsc. Gord Dibben MS Excel MVP On Tue, 25 Jul 2006 11:08:02 -0700, mja wrote: My spread sheet is over 33000 row long, I have an employee name in a1 and need that same name copied into cells a2-a9 and then an other employee name a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish so that I can use a filer to extract certain information but keep it attahced to teh right employee. Thanks "mja" wrote: I would like to write a macro to copy information from one cell to several others and then copy other info from one cell to several other cells |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
using marcos to copy large amounts of info
Thank you so much. You saved me a ton of time
"Gord Dibben" wrote: Do not type the code into a module.....copy it from the post and paste it in. With workbook open hit ALT + F11 to get to Visual Basic Editor. CTRL + r to open Project Explorer. Select your workbook/project and right-clickInsertModule. Paste the code in there. Add the line Option Explicit above the top line. ALT + q to return to Excel window. F8 to run the macro on the activecell column. Gord On Fri, 4 Aug 2006 14:05:02 -0700, mja wrote: Thank you for your response. I have tried this and I'm sure there is just one little thing that I do not have right but when I try to run teh macro I get and error message that says "invalid outside procedure" and it highlights where it is "Set wks = ActiveSheet" I have typed the macro as you sent it but I must not have it right. Thanks again "Gord Dibben" wrote: This macro to fill blank cells in chosen Column will do the trick if I read this right. Sub Fill_Blanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above Dim wks As Worksheet Dim Rng As Range Dim LastRow As Long Dim col As Long Set wks = ActiveSheet With wks col = ActiveCell.Column 'or 'col = .range("b1").column Set Rng = .UsedRange 'try to reset the lastcell LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set Rng = Nothing On Error Resume Next Set Rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Rng Is Nothing Then MsgBox "No blanks found" Exit Sub Else Rng.FormulaR1C1 = "=R[-1]C" End If 'replace formulas with values With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub You could also do it manually.......... Select Column A and F5SpecialBlanksOK Enter an = sign in active cell then point to cell above and hit CTRL + ENTER. Copy Column A and Paste SpecialValuesOKEsc. Gord Dibben MS Excel MVP On Tue, 25 Jul 2006 11:08:02 -0700, mja wrote: My spread sheet is over 33000 row long, I have an employee name in a1 and need that same name copied into cells a2-a9 and then an other employee name a13 and need it coped to cells a14-a20 and so forth. I'm trying to accomplish so that I can use a filer to extract certain information but keep it attahced to teh right employee. Thanks "mja" wrote: I would like to write a macro to copy information from one cell to several others and then copy other info from one cell to several other cells |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reminder notifications in a column | Excel Discussion (Misc queries) | |||
Excel won't copy outside sheet | Excel Discussion (Misc queries) | |||
Copy Function | Excel Discussion (Misc queries) | |||
Copy info from worksheet 1 to other sheets | Excel Worksheet Functions | |||
Help with large amounts of data and formulas | Excel Worksheet Functions |