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

VBA Copy Paste not working

Hi Expert,
Just wondering what is the problem with this code ? As i have a workbook with sheets name 1, 2, 3, 4, 5 and also i have a target workbook with the same sheets name. All i am doing is copying data from sheets (1,2,3...) and pasting it to corresponding sheets in target workbook. But the below code is giving me the desired result.


Code:
Sub test()

Dim myarr() As Variant
Dim wkb1, wkb2 As Workbook
Dim lastrow As Long

Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("C:\Users\Niraj.Baraili\Desktop\Target.xlsb")


myarr = Array(1, 2, 3, 4, 5)

For Each shtName In myarr
    
    With wkb1.Worksheets(shtName)
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row
        Set srcrange = Range("A2:CH" & lastrow)
    End With
            
    With wkb2.Worksheets(shtName)
        lastrow1 = Cells(Rows.Count, "A").End(xlUp).Row
        Range("A2:CH" & lastrow1).ClearContents
        Set DestRange = Range("A2")
    End With
    
    srcrange.Copy DestRange
    
Next shtName

End Sub
 
You are using array of integers. Worksheets() will accept either index# or string.

Since you are supplying it integer value, it interprets it as Worksheet index# rather than sheet name. Either nest each element in the array with "", or use CStr() as you supply the value to Worksheets().
 
Thank you very much for the insights.

After modification as per your advice, it's perfectly giving me the desired result.
I'm surprised.
You're missing some (5) dots in this bit of code:
Code:
    With wkb1.Worksheets(shtName)
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row
        Set srcrange = Range("A2:CH" & lastrow)
    End With
           
    With wkb2.Worksheets(shtName)
        lastrow1 = Cells(Rows.Count, "A").End(xlUp).Row
        Range("A2:CH" & lastrow1).ClearContents
        Set DestRange = Range("A2")
    End With
which probably should be:
Code:
    With wkb1.Worksheets(shtName)
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set srcrange = .Range("A2:CH" & lastrow)
    End With
           
    With wkb2.Worksheets(shtName)
        lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A2:CH" & lastrow1).ClearContents
        Set DestRange = .Range("A2")
    End With
 
Back
Top