Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special issues
' 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special issues
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special issues
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special issues
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special issues
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 " wrote: ' 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special issues
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
Cut and Paste using Macro gives paste special method error | Excel Programming | |||
How do I capture user paste action and convert to Paste Special | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |