Jump back and forth between two sheets to copy values

Hello,

I am having trouble in making this code work:

Sub newsheet()
'Allows the user to copy the active sheet and clear contents in columns payments and advances of each product.
    
    Application.ActiveWorkbook.ActiveSheet.Select
    Application.ActiveWorkbook.ActiveSheet.Copy After:=ActiveSheet
    Range("C8:D38,H8:I38,M8:N38,R8:S38,W8:X38,AB8:AC38,AG8:AH38,AL8:AM38").Select
    Selection.ClearContents
    ActiveWorkbook.ActiveSheet.Range("A1").Select
    
    
    Dim wks As Worksheet
    Dim rng As Range
    
    Set wks = Application.ActiveWorkbook.ActiveSheet.Previous
    Set rng = wks.Range("E39")
    ' copies ending balance of previous sheet to beg. balance of newly created sheet.
    
    Do While Not IsEmpty(rng)
        rng.Copy
    
        Application.ActiveWorkbook.Worksheets(wks.Index + 1).ActiveCell.Offset(-32, 0).PasteSpecial xlPasteValues
    
        Set rng = rng.Offset(0, 4).Select
    Loop 'Loops back through the previous sheet to until it doesnt find any product anymore.
    
End Sub

The error message (Object doesnt support this propety or method) occurs at the end of the code:

Application.ActiveWorkbook.Worksheets(wks.Index + 1).ActiveCell.Offset(-32, 0).PasteSpecial xlPasteValues

Without seeing your actual workbook and being able to test, this is my best guess at this time. You have already defined wks as a worksheet, so you do not need to name it as part of the worksheets in the workbook. It is already a variable.

Application.ActiveWorkbook.wks.Index + 1.ActiveCell.Offset(-32, 0).PasteSpecial xlPasteValues

Like I said, without seeing your workbook or a representative sample of it, it is difficult to determine what the issue is and how to fix it Also, it would be very helpful if you explained what you are trying to do instead of us trying to interpret what your non-working code is meant to do. A simple detailed explanation will go a long way to solving this.

In your line of code, excel is looking for a destination cell to paste into. You have indicated ActiveCell, however, it does not appear that you have selected an active cell in the destination. ActiveCell does not work in this situation. Is there a specific destination cell you are looking to populate. Probably need to set it up as a variable.

It would also help if there was dummy data to work with instead of blank cells. And as I indicated earlier, a brief explanation would go a long way.

I am trying to carry over the ending balance (copied sheet) to the new sheet (pasted sheet) in the beginning balance with its respective product#.
I got it with this code:

    
Dim wks As Worksheet
    Dim rng As Range
    Dim rng2 As Range
    Set wks = Application.ActiveWorkbook.ActiveSheet.Previous
    Set rng = wks.Range("E39")
    Set rng2 = Application.ActiveWorkbook.ActiveSheet.Range("E7")
    ' copies ending balance of previous sheet to beg. balance of newly created sheet.
    
    Do While Not IsEmpty(rng)
        rng.Copy
        rng2.PasteSpecial xlPasteValues
        
        Set rng = rng.Offset(0, 4)
        Set rng2 = rng2.Offset(0, 4)
    Loop 'Loops back through the previous sheet until it doesnt find any product anymore.
    
End Sub