Compile Error

[akash kothari]

Capture.JPGHello Friend I am trying to create a Macro where while tesiting i am getting the below compile Error.

Column B is Employee Name
Column E is the Project Details (A, B, C, D)
Column G, H, I, J, K Are the expense (Airport Tax, Hotel , Fare, Other)

I wanted to copy these details in the Sheets Name based on Projects (A, B, C, D)

I have attached the IMAGE



Below is the Macro

Please Login or Register  to view this content.

Sub traveldtls()

Dim ws As Worksheet
Dim lastrow As Long
Dim i As Integer
Dim expdtls1 As Integer
Dim expdtls2 As Integer
Dim EMPname As String

lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row


For i = 2 To lastrow

If Cells(i, 5) = "A" Then
Range(Cells(i, 2), Cells(i, 6), Cells(i, 7)).Select
Sheets(2).Range(Cells(4, 6), Cells(4, 7), Cells(4, 7)).PasteSpecial

End If
Next i


End Sub

Please Login or Register  to view this content.

[TMS]

The .Select should be .Copy


Please add Code Tags to your post.


Regards, TMS

[akash kothari]

Hi TMS

Thank you for your quick reply

But still below code throws and Compile error

Range(Cells(i, 2), Cells(i, 6), Cells(i, 7)).Copy

Akash

[AB33]

The range object can only take 2 arguments, a row and a column. Remove one of the cells or use union instead.

[TMS]

@AB33: ah, good spot ... I went for the obvious without looking closely at the range. Strictly speaking, it's not row and column, it's start and end cells. And it could be one parameter if the string is a text string with a valid cell or range address.

That said, the PasteSpecial won't work either, same reason. And I note that two of the cell addresses are the same. And, being picky, this loop is just going to keep overwriting the same cell(s).

You could have something like this, which will compile but maybe won't do what you want:

Please Login or Register  to view this content.

Regards, TMS

[akash kothari]

Ok, I got this; But this Macro is not giving me the output as i was expecting.
will you be able to assist on this?

Akash