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

tycho

New Member
Joined
Jul 18, 2010
Messages
2
Hello,

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

Code:
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

Code:
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)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,510
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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

New Member
Joined
Jul 18, 2010
Messages
2
Thank you very much for help, that did the trick!

Regards
Tihomir
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top