VBA: Copy from one range to another without ClipBoard

[Bill Bisco]

Hi all. All I want to do is copy one column of data from one worksheet to another without using the Clipboard. I can't get my line highlighted in red to work. Can you help?

Dim Sws As Worksheet, Cws As Worksheet 'Starting Worksheet, Copy Worksheet
Dim Sr As Integer, Cr As Integer    'Starting Worksheet Row, Copy Worksheet Row
Dim Sc As Integer, Cc As Integer    'Starting Worksheet Column, Copy Worksheet Column
Dim Clr As Long 'Copy the last row of the Worksheet
Dim Str As Long 'Starting worksheet total rows
Dim Sar As Long 'Starting worksheet adjusted rows
Set Sws = Sheets("MaterialFlow")
Set Cws = Sheets("Input PFEP")

Sr = 8
Sc = 4
Cr = 2
Cc = 15
Clr = Cells(Rows.Count, Cc).End(xlUp).Row

'Sws.Range(Cells(Sr, Sc), Cells(Sr, Sc)) = Cws.Range(Cells(Cr, Cc), Cells(Clr, Cc))
Str = Clr - Cr
Sar = Str - Sr
[COLOR=#ff0000]Sws.Range(Cells(Sr, Sc), Cells(Sar, Sc)) = Cws.Range(Cells(Cr, Cc), Cells(Clr, Cc))[/COLOR]

 

[Rick Rothstein]

Sws.Range(Cells(Sr, Sc), Cells(Sar, Sc)) = Cws.Range(Cells(Cr, Cc), Cells(Clr, Cc)).Value

Try adding the .Value property that I show in blue above.

 

[RoryA]

You're also not properly qualifying everything:

Sws.Range(sws.Cells(Sr, Sc), sws.Cells(Sar, Sc)).value = Cws.Range(cws.Cells(Cr, Cc), cws.Cells(Clr, Cc)).value

 

[Rick Rothstein]

You're also not properly qualifying everything:

Sws.Range(sws.Cells(Sr, Sc), sws.Cells(Sar, Sc)).value = Cws.Range(cws.Cells(Cr, Cc), cws.Cells(Clr, Cc)).value

Good point! I didn't even notice that.

 

[Bill Bisco]

Thank you both!

I ended up having to modify the code again to this:

Sws.Range(Sws.Cells(Sr, Sc), Sws.Cells(Sar, Sc)).Value = Cws.Range(Cws.Cells(Cr, Cc), Cws.Cells(Clr, Cc)).Value

I forgot that you have to enter in the Worksheet name again for R1C1 Format!