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

[PJ CHILDS]

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

 

[Paul Ked]

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

 

[PJ CHILDS]

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

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

 

[Paul Ked]

That's great! I'll have a look in a bit, got some breakfast to make!

 

[Paul Ked]

It could be that you are not referencing your cells. eg:

    Worksheets("MASTER TIF LISTS").Range([COLOR=#ff0000]Worksheets("MASTER TIF LISTS").[/COLOR]Cells(4, newstart)

 

[PJ CHILDS]

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.

 

[Fluff]

Hi & welcome to the board.
You weren't fully qualifying all your ranges, try

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

 

[PJ CHILDS]

Thank you very much Fluff, this works a treat

 

[Rick Rothstein]

Hi & welcome to the board.
You weren't fully qualifying all your ranges, try

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...

.Range("F4:H33,J4:K33").ClearContents

 

[Fluff]

Thank you very much Fluff, this works a treat

Glad we could help & thanks for the feedback