Pasting values to another worksheet

[lampis]

Hello!

I have some problems with pasting values from one sheet to another.

This is my code:

Public Sub SaveCSV()
Dim sPath As String, sFilename As String, sPath_and_filename As String, start_date As String, end_date As String, data_cell As String
Dim i As Integer, j As Integer, n As Integer, row_start_date As Integer, row_end_date As Integer

n = Cells(65536, 1).End(xlUp).Row

With Sheets("Data selection")
start_date = .Cells(2, 4).Value
end_date = .Cells(3, 4).Value

For i = 15 To n
If .Cells(i, 1).Value = start_date Then
row_start_date = i
End If
If .Cells(i, 1).Value = end_date Then
row_end_date = i
End If
Next i


For i = 2 To 30
Range(Cells(row_start_date, i), Cells(row_end_date, i)).Select
Selection.Copy
Sheets("opt_input").Cells(1, i).Paste
Next i

End with

I get the error msg "Object doesn't support this property or method", highlighting "Sheets("opt_input").Cells(1, i).Paste".

What am I doing wrong?

Thanks.

 

[VoG]

Try

.Range(.Cells(row_start_date, i), .Cells(row_end_date, i)).Copy Destination:=Sheets("opt_input").Cells(1, i)

 

[lampis]

Thanks for that!

Two other problems I encountered now:

I want to clean the "opt_input" sheet before I paste stuff into it. I tried putting "Sheets("opt_input").Range(Cells(1, 1), Cells(65536, 50)).ClearContents" first in my sub. Get an error msg though: "Application-defined or object-defined error". Any idea why?

Also I want to save the opt_input sheet as an csv. This is my code:

With Sheets("opt_input")
sPath = "C:\"
sFilename = "opt" & 1 & ".csv"
sPath_and_filename = sPath & sFilename

i = 0
Do While Dir(sPath_and_filename) <> ""
i = i + 1
sFilename = "opt" & 1 + i & ".csv"
sPath_and_filename = sPath & sFilename
Loop

ActiveSheet.Copy
ActiveWorkbook.SaveAs filename:= _
sPath_and_filename, FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWorkbook.Close
End With

The problem is that it saves the "Data selection" sheet, not the opt_input. Maybe because I run my VBA code from a button located at the "Data selection" sheet... but at the same time, i tried with the "With Sheets("opt_input")" code but doesn't work.

Do you know what the problem is?

 

[VoG]

Try

Sheets("opt_input").Range(Sheets("opt_input").Cells(1, 1), Sheets("opt_input").Cells(65536, 50)).ClearContents

and

Sheets("opt_input").Copy

 

[lampis]

Thanks a lot! Fast replies, me like