MACRO PASTE CELL to COLUMN, SKIP ROWS

  • Thread starter Thread starter Dan
  • Start date Start date
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
 
Sorry, seems could not see missed 1st example, full repost:

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.

--------------

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


1ST EXAMPLE:

Sub PastecellE() 'alt-E (paste cell EQ/ Formula)
r = ActiveCell.Row 'row
c = ActiveCell.Column 'cell
LastRow = Range("C4").Value

For Each c In Range(Cells(r, c), Cells(LastRow, c))
If Cells(c.Row, "A").Value <> "." Then
c.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Next c
End Sub


'xxxxxxxxxx

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
 
Back
Top