Copying a variable length range of cells to the bottom of another list

PJ CHILDS

New Member
Joined
Jan 28, 2018
Messages
4
I am trying to copy a range of data from one sheet to another. the range of data being copied is always a different length depending on the date selected, also the copied data has to be pasted to the bottom of the master copy list thereby creating a full list for the user to search for specific data. I keep getting the error below when i try to run the code.

'Run-time error '1004' Application-defined or Object defined error

Sub P_8_move()'Dim newstart As Integer
'Dim length As Integer
'Dim newend As Integer
newstart = Worksheets("MASTER TIF LISTS").Range("b1").Value
length = Worksheets("MASTER TIF LISTS").Range("b2").Value
newend = Worksheets("MASTER TIF LISTS").Range("b3").Value
Worksheets("MASTER TIF LISTS").Range(Cells(4, newstart), Cells(newend, 13)).Value = _
Worksheets("Period 8 Data").Range(Cells(4, 4), Cells(newend, 11)).Value
Worksheets("period 8 data").Range("F4:H33").ClearContents
Worksheets("period 8 data").Range("J4:K33").ClearContents
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi and welcome to the forum.

Can you post all of your code (including the End Sub) and use code tags. To use code tags you select all your code and click the # button.

Please also indicate the line your error is appearing on.

Thanks
 
Upvote 0
Hi and welcome to the forum.

Can you post all of your code (including the End Sub) and use code tags. To use code tags you select all your code and click the # button.

Please also indicate the line your error is appearing on.

Thanks

Code:
Sub P_8_move()Dim newstart As Integer
Dim length As Integer
Dim newend As Integer
newstart = Worksheets("MASTER TIF LISTS").Range("b1").Value
length = Worksheets("MASTER TIF LISTS").Range("b2").Value
newend = Worksheets("MASTER TIF LISTS").Range("b3").Value
[COLOR=#ff0000]Worksheets("MASTER TIF LISTS").Range(Cells(4, newstart), Cells(newend, 13)).Value = _[/COLOR]
[COLOR=#ff0000]    Worksheets("Period 8 Data").Range(Cells(4, 4), Cells(newend, 11)).Value[/COLOR]
        Worksheets("period 8 data").Range("F4:H33").ClearContents
            Worksheets("period 8 data").Range("J4:K33").ClearContents
End Sub

is this what you want, i am new to doing this, sorry
 
Upvote 0
It could be that you are not referencing your cells. eg:

Code:
    Worksheets("MASTER TIF LISTS").Range([COLOR=#ff0000]Worksheets("MASTER TIF LISTS").[/COLOR]Cells(4, newstart)
 
Upvote 0
Sorry to say, that does not work, even tried taking out the first worksheets and moving it to your example but got a global failed error.
 
Upvote 0
Hi & welcome to the board.
You weren't fully qualifying all your ranges, try
Code:
Sub P_8_move()

   Dim newstart As Integer
   Dim length As Integer
   Dim newend As Integer
   Dim MTLws As Worksheet
   
   Set MTLws = Worksheets("MASTER TIF LISTS")
   newstart = MTLws.Range("b1").Value
   length = MTLws.Range("b2").Value
   newend = MTLws.Range("b3").Value
   
   With Worksheets("Period 8 Data")
      MTLws.Range(MTLws.Cells(4, newstart), MTLws.Cells(newend, 13)).Value = _
       .Range(.Cells(4, 4), .Cells(newend, 11)).Value
      .Range("F4:H33").ClearContents
      .Range("J4:K33").ClearContents
   End With
End Sub
 
Upvote 0
Hi & welcome to the board.
You weren't fully qualifying all your ranges, try
Code:
Sub P_8_move()

   Dim newstart As Integer
   Dim length As Integer
   Dim newend As Integer
   Dim MTLws As Worksheet
   
   Set MTLws = Worksheets("MASTER TIF LISTS")
   newstart = MTLws.Range("b1").Value
   length = MTLws.Range("b2").Value
   newend = MTLws.Range("b3").Value
   
   With Worksheets("Period 8 Data")
      MTLws.Range(MTLws.Cells(4, newstart), MTLws.Cells(newend, 13)).Value = _
       .Range(.Cells(4, 4), .Cells(newend, 11)).Value
[B][COLOR="#FF0000"]      .Range("F4:H33").ClearContents
      .Range("J4:K33").ClearContents
[/COLOR][/B]   End With
End Sub
For the red highlighted lines of code, why not just this instead...
Code:
.Range("F4:H33,J4:K33").ClearContents
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,718
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top