D
Dan
hi, I have a macro i am working with to paste formulas from a cell, down a
sheet.
i have 3 examples below, the 1st one works, it only pastes once cell at a
time. is there a way to paste to all cells in a column range, all at once.
(thanks)
1st does 1 cell at a time, is it possible to do all cells at once, as the
2nd example
- else does everything need: allow pick any Column (dynamic), to paste any
cell that is already been clicked as copy (dynamic).
- work cell C4 has last row by: =ROW($A$2075)
2nd does not skip rows where column A has a period ".", but does paste to
all cells at once. - cell C4 has range of rows by:
=ROW($A$2075)-ROW($A$242)-1
3rd example was not setup for cell to column, not sure if is an answer idea.
xxxxxxxxxx
1 i want to manually copy new formula from 1 cell at top,
2 manually select any column, 'dynamic' range of rows, or end row..
(i will manually select 1st cell/ row in col desired for paste col),
3 paste formula's, skip rows where col A has a period "."
4 ALL TO PASTE ALL AT ONCE, instead of 1 cell at a time, down a column
Sub test() 'alt-T (test)
'1st 4 lines below work, but does not skip lines with period "." in col A
'and does what want: pick any col for hit alt-T shorcut to paste
'2ND EXAMPLE:
'Dim C4 As String
'C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select
'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'3RD EXAMPLE:
Dim cell As Range
Dim ws As Worksheet
Dim C4 As Long 'this is a number since using it as an offset
Set ws = Worksheets("sym") ' Set ws = Worksheets("Sheet1")
C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'ERROR
For Each cell In ws.Range(C4) '(err: METHOD RANGE OF OBJECT WORKSHEET
FAILED) 'column manual selected / rows C4
If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
Else
'PROBLEM:
With ws.Range(ActiveCell, ws.Range(ActiveCell, ActiveCell &
cell.Row).Offset(C4, 0)) 'MODIFY
'With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0)) 'WRONG, not just col A
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select 'ORIGINAL
WORKS (in above) PASTE ALL ROWS: NOT WANTED
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End If
Next
End Sub
'xxxxxxxxxx SAMPLE original: (not want copy col to col, not sure otherwise
works)
'Sub test() 'alt-T (test)
' Dim cell As Range
' Dim ws As Worksheet
' Dim C4 As Long 'this is a number since you're using it
as an offset
' Set ws = Worksheets("Sheet1")
' C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
' For Each cell In ws.Range(C4)
' If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
' Else
'no ws.Range("A" & cell.Row).Copy ' have no idea what you want to paste
' With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0))
' .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' 'the above code pastes over values in column A.
' End With
' End If
' Next
'End Sub
sheet.
i have 3 examples below, the 1st one works, it only pastes once cell at a
time. is there a way to paste to all cells in a column range, all at once.
(thanks)
1st does 1 cell at a time, is it possible to do all cells at once, as the
2nd example
- else does everything need: allow pick any Column (dynamic), to paste any
cell that is already been clicked as copy (dynamic).
- work cell C4 has last row by: =ROW($A$2075)
2nd does not skip rows where column A has a period ".", but does paste to
all cells at once. - cell C4 has range of rows by:
=ROW($A$2075)-ROW($A$242)-1
3rd example was not setup for cell to column, not sure if is an answer idea.
xxxxxxxxxx
1 i want to manually copy new formula from 1 cell at top,
2 manually select any column, 'dynamic' range of rows, or end row..
(i will manually select 1st cell/ row in col desired for paste col),
3 paste formula's, skip rows where col A has a period "."
4 ALL TO PASTE ALL AT ONCE, instead of 1 cell at a time, down a column
Sub test() 'alt-T (test)
'1st 4 lines below work, but does not skip lines with period "." in col A
'and does what want: pick any col for hit alt-T shorcut to paste
'2ND EXAMPLE:
'Dim C4 As String
'C4 = Range("C4") 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select
'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'3RD EXAMPLE:
Dim cell As Range
Dim ws As Worksheet
Dim C4 As Long 'this is a number since using it as an offset
Set ws = Worksheets("sym") ' Set ws = Worksheets("Sheet1")
C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
'ERROR
For Each cell In ws.Range(C4) '(err: METHOD RANGE OF OBJECT WORKSHEET
FAILED) 'column manual selected / rows C4
If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
Else
'PROBLEM:
With ws.Range(ActiveCell, ws.Range(ActiveCell, ActiveCell &
cell.Row).Offset(C4, 0)) 'MODIFY
'With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0)) 'WRONG, not just col A
'Range(ActiveCell, ActiveCell.Offset(C4, 0)).Select 'ORIGINAL
WORKS (in above) PASTE ALL ROWS: NOT WANTED
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End If
Next
End Sub
'xxxxxxxxxx SAMPLE original: (not want copy col to col, not sure otherwise
works)
'Sub test() 'alt-T (test)
' Dim cell As Range
' Dim ws As Worksheet
' Dim C4 As Long 'this is a number since you're using it
as an offset
' Set ws = Worksheets("Sheet1")
' C4 = ws.Range("C4").Value 'C4 has: =ROW($A$2058)-ROW($A$228)-1
' For Each cell In ws.Range(C4)
' If ws.Range("A" & cell.Row).Value = "." Then 'do nothing
' Else
'no ws.Range("A" & cell.Row).Copy ' have no idea what you want to paste
' With ws.Range("A" & cell.Row, ws.Range("A" &
cell.Row).Offset(C4, 0))
' .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' 'the above code pastes over values in column A.
' End With
' End If
' Next
'End Sub