Copy/Pasting ... Need Help ...

[Jlascu]

Hi,

Need help ...I'm trying to write a macro that copies data from one sheet to another, but needs to use R1C1 format as both are dynamic ranges. Below is the code and I highlighted where the error is ... any advice is greatly appreciated!

many thanks in advance

---


Dim x As Workbook
Dim y As Workbook
Dim sRangeERFormulas As Range
'Set both workbooks
Set x = Workbooks(sSPVFileName): Set y = Workbooks(sDPMPName)

'Pass # records to DPMP
y.Sheets("Ctrls").Range("D21").Value = x.Sheets("Pf").Range("C3").Value
icolumn_number = 4
iCtrls = 10
'Copy field headings


'Loops # fields in the portoflio table
For a = 1 To Worksheets("Ctrls").Range("C20").Value

'[THIS IS THE CASE LOGIC THAT IM TRYING TO OPTIMIZE THRU R1C1]


Select Case a
Case 1: sExceptionFormula = "=IF(ISBLANK(A6)," & """Upload""" & ",IF(B6<>A6," & """Error""" & "," & """OK""" & "))" _
': sCtrls = "K": sPf = "A"

Case 2: sExceptionFormula = "=IF((A6-B6)=0," & """OK""" & ",A6-B6)" _
: sCtrls = "L": sPf = "B"

Case 3: sCtrls = "M": sPf = "C"

Case 4: sExceptionFormula = "=IF((A6=B6)=True," & """OK""," & """Error"")": _
: sCtrls = "N": sPf = "D"

Case 5: sCtrls = "O": sPf = "E"

Case 6: sCtrls = "P": sPf = "F"

Case 7: sCtrls = "Q": sPf = "G"

Case 8: sCtrls = "R": sPf = "H"

Case 9: sCtrls = "S": sPf = "I"


End Select

'2 Loops: 1 to get records from DB & 2 to get records from Svcr
For b = 1 To 2

Select Case b
Case 1: iNoRecords = Worksheets("Ctrls").Range("C21").Value
Case 2: iNoRecords = Worksheets("Ctrls").Range("D21").Value
End Select

'Loops for # of records
For c = 1 To iNoRecords
Select Case b
'Case 1: y.Sheets("ER").Range("A" & c + 5).Value = y.Sheets("Ctrls").Range(sCtrls & c + 17).Value 'Add 17 to account for 17 rows in Controls page
Case 1:
'[ITS APPLIED HERE, BUT EITHER GIVES AN ERROR OR CELLS COME AS EMPTY]

y.Sheets("ER").Cells(1, c + 5).Value = y.Sheets("Ctrls").Cells(iCtrls, c + 17).Value



Case 2: y.Sheets("ER").Range("B" & c + 5).Value = x.Sheets("Pf").Range(sPf & c + 5).Value 'Add 5 to account for 5 rows in Svcr file
End Select
iCtrls = iCtrls + 1
Next

'Insert Exceptions formulas Cells(row_number, column_number)
Sheets("ER").Range("C6") = sExceptionFormula
Sheets("ER").Range("C6").Copy Sheets("ER").Range("C" & 7 & ":" & "C" & 5 + iNoRecords)

'Copy to the rest of the fields
With y.Sheets("ER")
.Range(.Cells(6, 3), .Cells(6 + iNoRecords, 3)).Copy
.Range(.Cells(6, icolumn_number + a), .Cells(14, icolumn_number + a)).PasteSpecial Paste:=xlPasteValues
End With


Next

Next
End Sub