Excel VBA: copying range from one worksheet to another produces 1004 error

[tycho]

Hello,

The following statement in my little VBA program produces the "Run-time error '1004': Application-defined or object-defined error":

Worksheets("calc_base(DO NOT DELETE)").Range(Cells(MW, 2),  Cells(MW, 2).End(xlToRight)).Copy  Destination:=Worksheets("Projekte").Cells(INPUT_ROW, MONTH_POSITION)

What I would like to do is:
After previously reading and calculating the integer values for MW, INPUT_ROW, MONTH_POSITION, I want to copy from first worksheet a part of row (no. of row = MW) from column 2 to the right until the end of data, and paste it to another worksheet, starting with cell on position (INPUT_ROW, MONTH_POSITION).

I apologize if I am not seeing the obvious -- it is my first VBA try. The whole code up to the failing point is below.

Thanks for any help!
T

Sub calculate_project_resources()
    ' declarations
    Dim MAX_ROWS As Integer
    Dim MAX_COLUMNS1 As Integer
    Dim MAX_COLUMNS2 As Integer
    Dim MAX_MONTH As Integer
    Dim INPUT_ROW As Integer
    Dim MW As String
    Dim START_MONTH As Date
    Dim MONTH_POSITION As Integer
    Dim STR As String
    Dim CNT As Integer
    Dim LOOP_CNT As Integer
    Dim JOB_CNT As Integer
        
        
    ' initializing parameters
    MAX_ROWS = 500
    MAX_COLUMNS1 = 50
    MAX_COLUMNS2 = 500
    MAX_MONTH = 168
    CNT = 7
    LOOP_CNT = 1
    JOB_CNT = 1
    
    ' setting the general application behaviour (no screen flickering,  clearing the clipboard)
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
 
    ' loop which search for input data in "Projekte" sheet
    Sheets("Projekte").Select
    For Each Cell In Range(Cells(12, 5), Cells(MAX_ROWS, 5))
        Cell.Activate
        If ActiveCell.Value = "NO" Or ActiveCell.Value = "NF" Then
           INPUT_ROW = ActiveCell.Row
           MW = Cells(INPUT_ROW, 2).Value
           START_MONTH = Cells(INPUT_ROW, 3).Value
        End If

        ' loop which finds the column of the start month
        Do Until Cells(11, CNT).Value = START_MONTH
           CNT = CNT + 1
        Loop
        MONTH_POSITION = CNT
        
        ' locating and copying data from the "calc_base(DO NOT DELETE)"  sheet
        Worksheets("calc_base(DO NOT DELETE)").Range(Cells(MW, 2),  Cells(MW, 2).End(xlToRight)).Copy  Destination:=Worksheets("Projekte").Cells(INPUT_ROW, MONTH_POSITION)

 

[Peter_SSs]

Welcome to the MrExcel board!

I haven't tested your code or my suggestion but I suspect your problem is to do with not referencing your ranges to specific sheets. Try this instead of the problem bit

With Worksheets("calc_base(DO NOT DELETE)")
    .Range(.Cells(MW, 2), .Cells(MW, 2).End(xlToRight)).Copy Destination:=Worksheets("Projekte").Cells(INPUT_ROW, MONTH_POSITION)
End With

 

[tycho]

Thank you very much for help, that did the trick!

Regards
Tihomir