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

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

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

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

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

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

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