Copying From One Sheet to another Q

[John]

I have this loop code below which copies from sheet "Coda Template" to sheet
"Coda", but it debugs at the line below and I don't know why

Range("a" & IngOutY) = Worksheets("Coda Template").Range("A" & IngPosY)

Appreciate any input

Thanks


Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean


Sheets("Coda").Select


lngPosY = 2 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "Document_Type"
Range("D1") = "Document_date"
Range("E1") = "Nominal"
Range("F1") = "Subaccount"
Range("G1") = "Level3"
Range("H1") = "Document_Value"
Range("I1") = "Document_Year"
Range("J1") = "Document_Period"
Range("K1") = "External_Text"
Range("L1") = "Quantity_1"
Range("M1") = "Description"


IngOutY = 2

Do While Len(Worksheets("Coda Template").Range("A" & lngPosY)) > 0
Range("a" & IngOutY) = Worksheets("Coda Template").Range("A" &
IngPosY)
Range("B" & IngOutY) = Worksheets("Coda Template").Range("B" &
IngPosY)
Range("C" & IngOutY) = Worksheets("Coda Template").Range("C" &
IngPosY)
Range("D" & IngOutY) = Worksheets("Coda Template").Range("D" &
IngPosY)
Range("E" & IngOutY) = Worksheets("Coda Template").Range("E" &
IngPosY)
Range("F" & IngOutY) = Worksheets("Coda Template").Range("F" &
IngPosY)
Range("G" & IngOutY) = Worksheets("Coda Template").Range("G" &
IngPosY)
Range("H" & IngOutY) = Round(Worksheets("Coda Template").Range("H" &
IngPosY), 2)
Range("I" & IngOutY) = Worksheets("Coda Template").Range("I" &
IngPosY)
Range("J" & IngOutY) = Worksheets("Coda Template").Range("J" &
IngPosY)
Range("K" & IngOutY) = Worksheets("Coda Template").Range("K" &
IngPosY)
Range("L" & IngOutY) = Worksheets("Coda Template").Range("L" &
IngPosY)
Range("M" & IngOutY) = Worksheets("Coda Template").Range("M" &
IngPosY)


IngPosY = IngPosY + 1
IngOutY = IngOutY + 1

Loop

 

[Ron de Bruin]

Hi John

Use .Value

Range("a" & IngOutY).Value = Worksheets("Coda Template").Range("A" & IngPosY).Value

 

[John]

Makes no difference Ron, still an Application defined error

 

[Bob Phillips]

Well to start you declare a variable lngPos Y and lngOutY, but use IngOutY &
IngPosY

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John]

Bingo, thanks Bob, I was 2 hours looking at that

 

[Tom Ogilvy]

I don't know, but try it this way:

lngPosY = 2 ' Starting row on starting sheet

With Worksheets("Coda")
.Range("A1") = "Document_Number"
.Range("B1") = "Line_Number"
.Range("C1") = "Document_Type"
.Range("D1") = "Document_date"
.Range("E1") = "Nominal"
.Range("F1") = "Subaccount"
.Range("G1") = "Level3"
.Range("H1") = "Document_Value"
.Range("I1") = "Document_Year"
.Range("J1") = "Document_Period"
.Range("K1") = "External_Text"
.Range("L1") = "Quantity_1"
.Range("M1") = "Description"




Do While Len(Worksheets("Coda Template").Range("A" & lngPosY)) > 0
.Range("a" & IngPosY).Resize(1,13).Value = _
Worksheets("Coda Template").Range("A" & lngPosY). _
.Resize(1,13).Value
lngPosY = lngPosY + 1
Loop
End With