Paste Special issues

[knox5731]

' Detail Machine Code, PCI, and Agreement type Column
'Set ws1 = Worksheets("Calc")
'Set ws2 = Worksheets("Generator")
Dim lastrowDMC As Long
Dim rDMC As Long, rrDMC As Long
rrDMC = 14
Dim DMC As Long
DMC = Range("I11").Copy
With ws2
lastrowDMC = .Cells(Rows.Count, "B").End(xlUp).Row
For rDMC = 14 To lastrowDMC
Range("I11").Select
Selection.Copy
ws2.Cells(rrDMC, "C") = Selection.PasteSpecial '_
'Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

rrDMC = rrDMC + 1
Next rDMC
End With

I'm trying to get I11 (a static cell) to copy in a loop to Column C
starting at row 14. However, when I try to copy I get the value "True"
in all of the cells cause I11 has a formula..So I want to paste special
values, but I keep getting syntax error when I uncomment the
'Paste:x1PasteValues....' parts....Help?

 

[Executor]

Hi,

For .Cells 2 Longs are used
first the row number
second the column number, not the letter(s) as shown on the worksheet

Furthermore you can use a ranges of cells at ones to fill with a value

You are close, try this:



Set ws1 = Worksheets("Calc")
Set ws2 = Worksheets("Generator")

Dim lastrowDMC As Long
Dim DMC As Long
DMC = ws1.Range("I11").Value
With ws2
lastrowDMC = .Cells(Rows.Count, "B").End(xlUp).Row
.Range(Cells(14, 3), Cells(lastrowDMC, 3)).Value = DMC
End With


Hoop this helps,


Executor

 

[Executor]

Hi,


For .Cells 2 Longs are used
first the row number
second the column number, not the letter(s) as shown on the worksheet


Furthermore you can use a ranges of cells at ones to fill with a value


You are close, try this:


Set ws1 = Worksheets("Calc")
Set ws2 = Worksheets("Generator")


Dim lastrowDMC As Long
Dim DMC As Long
DMC = ws1.Range("I11").Value
With ws2
lastrowDMC = .Cells(Rows.Count, 2).End(xlUp).Row
.Range(Cells(14, 3), Cells(lastrowDMC, 3)).Value = DMC
End With


Hoop this helps,


Executor

 

[knox5731]

That looks good, but it doesn't allow me to increment to go to the next
cell cause there is no loop in there to go down the column, and stop at
the last row that I need.....I need to put some value in there about
that.....it doesn't increment the 14 you hard coded in there....

 

[Guest]

Hi,

Try this: (note the "dot" and the lack of "selection" in the PasteSpecial
line.)

Range("I11").Select
Selection.Copy
ws2.Cells(rrDMC, "C") .PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Art

 

[Dave Peterson]

Untested, but it did compile:

Option Explicit
Sub testme()
' Detail Machine Code, PCI, and Agreement type Column

Dim lastrowDMC As Long
Dim rDMC As Long
Dim rrDMC As Long
Dim DMC As Long

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets("Calc")
Set ws2 = Worksheets("Generator")

With ws2
lastrowDMC = .Cells(Rows.Count, "B").End(xlUp).Row
For rDMC = 14 To lastrowDMC
.Range("I11").Copy 'or ws1.range("I11").copy '????
ws2.Cells(rrDMC, "C").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next rDMC
End With
End Sub

You don't have to initialize rDMC. The For/Next will do that. You don't have
to increment it, either.

I wasn't sure where i11 was, ws1 or ws2???

 

[Executor]

Hi,

How about this:

With ws2
.Activate
lastrowDMC = .Cells(Rows.Count, 2).End(xlUp).Row
.Range(Cells(14, 3), Cells(lastrowDMC, 3)).Value = DMC
.Cells(lastrowDMC + 1, 3).Select
End With

This will set the focus to the first free row on sheet "Generator"