• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What is wrong with this VBA

W

Wskip9449

New Member
I've tried many different ideas but none have work. I'm really trying to understand VBA's but it seems I'm strunggling.

Code: 
Sub PostToOct_21()

       Dim WS1 As Worksheet
       Dim WS13 As Worksheet
       Set WS1 = Worksheets("Invoice")
       Set WS13 = Worksheets("Oct_21")
    
     ' figre out which row is the next row
     Dim lastRow As Long
  
    'Where is last row of data?
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
    'Apply formula
    Range("K9:K" & lastRow).Formula = ("=SUM("F9:J9"))
    
' Write the Important values to Oct_21
    
        WS13.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("E3"), WS1.Range("Y2"), WS1.Range("E4"), Range("InvTot"))
          
           Dim rngCopy As Range, rngPaste As Range

            With ActiveSheet
                Set rngCopy = .Range(.Range("A9:C9"), .Cells(1, Columns.Count).End(xlToLeft))
                Set rngPaste = .Range(.Range("A10:C10"), .Cells(Rows.Count, 1).End(xlUp)).Resize(, rngCopy.Columns.Count)

End With

End Sub
 
A few things that may cause problems... see added comments
Code: 
Sub PostToOct_21()

       Dim WS1 As Worksheet
       Dim WS13 As Worksheet
       Set WS1 = Worksheets("Invoice")
       Set WS13 = Worksheets("Oct_21")
    
     ' figre out which row is the next row
     Dim lastRow As Long
 
    'Where is last row of data?
    'Good question: last row of data on which worksheet?
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
    'Apply formula
    'When writing a formula directly to a worksheet, you don't use the quotation marks for the range
    ' "=SUM(F9:J9)"
    'Onto which worksheet are you writing the formula?
    Range("K9:K" & lastRow).Formula = ("=SUM("F9:J9"))
    
' Write the Important values to Oct_21
        'As written, the variable nextRow = 0
        WS13.Cells(nextRow, 1).Resize(1, 4).Value = Array(WS1.Range("E3"), WS1.Range("Y2"), WS1.Range("E4"), Range("InvTot"))
          
           Dim rngCopy As Range, rngPaste As Range
            
            'Avoid using 'ActiveSheet' - name or reference the worksheet
            With ActiveSheet
                Set rngCopy = .Range(.Range("A9:C9"), .Cells(1, Columns.Count).End(xlToLeft))
                Set rngPaste = .Range(.Range("A10:C10"), .Cells(Rows.Count, 1).End(xlUp)).Resize(, rngCopy.Columns.Count)

End With

End Sub
 
RDAngelo said:
A few things that may cause problems... see added comments
Code: 
Sub PostToOct_21()

       Dim WS1 As Worksheet
       Dim WS13 As Worksheet
       Set WS1 = Worksheets("Invoice")
       Set WS13 = Worksheets("Oct_21")
   
     ' figre out which row is the next row
     Dim lastRow As Long

    'Where is last row of data?
    'Good question: last row of data on which worksheet?
    lastRow = WS13.Cells(Rows.Count, "A").End(xlUp).Row

    'Apply formula
    'When writing a formula directly to a worksheet, you don't use the quotation marks for the range
    ' "=SUM(F9:J9)"
    'Onto which worksheet are you writing the formula?
    WS13.Range("K9:K" & lastRow).Formula "=SUM(F9:J9}"
   
' Write the Important values to Oct_21
        'As written, the variable nextRow = 0 'I don't understand this.
        WS13.Cells(nextRow, 1).Resize(1, 4).Value = Array(WS1.Range("E3"), WS1.Range("Y2"), WS1.Range("E4"), Range("InvTot"))
         
           Dim rngCopy As Range, rngPaste As Range
           
            'Avoid using 'ActiveSheet' - name or reference the worksheet
            With ActiveSheet
                Set rngCopy = .Range(.Range("A9:C9"), .Cells(1, Columns.Count).End(xlToLeft))
                Set rngPaste = .Range(.Range("A10:C10"), .Cells(Rows.Count, 1).End(xlUp)).Resize(, rngCopy.Columns.Count)

End With

End Sub
Click to expand...

I made the changes but still did not work.
 
RDAngelo said:
What did not work? Please be more specific.
Click to expand...

This section.

>>> use code - tags <<<
Code: 
WS13.Cells(nextRow, 1).Resize(1, 4).Value = Array(WS1.Range("E3"), WS1.Range("Y2"), WS1.Range("E4"), Range("InvTot"))
        
           Dim rngCopy As Range, rngPaste As Range
          
            'Avoid using 'ActiveSheet' - name or reference the worksheet
            With ActiveSheet
                Set rngCopy = .Range(.Range("A9:C9"), .Cells(1, Columns.Count).End(xlToLeft))
                Set rngPaste = .Range(.Range("A10:C10"), .Cells(Rows.Count, 1).End(xlUp)).Resize(, rngCopy.Columns.Count)
 
Last edited by a moderator:
Try...
Code: 
Sub PostToOct_21()
    Dim WS1 As Worksheet, WS13 As Worksheet
    Dim lastRow As Long
    Dim rngCopy As Range, rngPaste As Range

    Set WS1 = Worksheets("Invoice")
    Set WS13 = Worksheets("Oct_21")
    
    With WS13
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("K9:K" & lastRow).Formula = "=SUM(F9:J9)"
        .Cells(lastRow + 1, 1).Resize(1, 4).Value = Array(WS1.Range("E3"), WS1.Range("Y2"), WS1.Range("E4"), Range("InvTot"))
    End With
    
    With ActiveSheet
        Set rngCopy = .Range(.Range("A9:C9"), .Cells(1, Columns.Count).End(xlToLeft))
        Set rngPaste = .Range(.Range("A10:C10"), .Cells(Rows.Count, 1).End(xlUp)).Resize(, rngCopy.Columns.Count)
    End With
    
End Sub
 
You must log in or register to reply here.
Back
Top