Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying From One Sheet to another Q
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying From One Sheet to another Q
Hi John
Use .Value Range("a" & IngOutY).Value = Worksheets("Coda Template").Range("A" & IngPosY).Value -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying From One Sheet to another Q
Makes no difference Ron, still an Application defined error
"Ron de Bruin" wrote in message .. . Hi John Use .Value Range("a" & IngOutY).Value = Worksheets("Coda Template").Range("A" & IngPosY).Value -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying From One Sheet to another Q
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying From One Sheet to another Q
Bingo, thanks Bob, I was 2 hours looking at that
"Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying From One Sheet to another Q
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 -- Regards, Tom Ogilvy "John" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
Copying the repeated data of the previous sheet to the next sheet | Excel Discussion (Misc queries) | |||
Dynamic column chart - copying from Sheet to Sheet. | Charts and Charting in Excel | |||
Copying cells from on sheet to another sheet (via sheet module) | Excel Programming | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |