Title: I've wanted to copy and paste specific data on existing worksheet from workbook loaded
Tags: excel;vba
Question: I've wanted to copy and paste specific data on existing worksheet from workbook loaded.
Code is running till get to below row..
(Please find full code on below)
rng.Copy worksheet(&quot;WMS&quot;).Cells(j, 39)
I guess it has problem with this
worksheet(&quot;WMS&quot;) (WMS worksheet is existing worksheet)in with loop but has no clue to solve this problem.
Would you give me advise what should I try?
thanks.
Private Sub btnMerge_Click()
Dim WB As Workbook
Dim WS As Worksheet: Dim toWS As Worksheet
Dim rng As Range
Dim i As Long: i = 0: Dim j As Long
Dim endCol As Long: Dim endRow As Long
Dim strWS As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Me.lstWB.ListCount = 0 Then
MsgBox &quot;No file have selected&quot;
Exit Sub
End If
Set toWS = ActiveSheet
j = toWS.Cells(toWS.Rows.Count, 1).End(xlUp).Row
For i = 0 To Me.lstWB.ListCount - 1
Set WB = Application.Workbooks.Open(Me.lstWB.List(i))
For Each WS In WB.Worksheets
With WS
endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range(.Cells(2, 1), .Cells(2, endRow))

rng.Copy worksheet(&quot;WMS&quot;).Cells(j, 39)
j = j + rng.Rows.Count
End With
Next
WB.Close
MsgBox &quot;Done&quot;
Unload Me
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Comment: It's `Worksheets`, not `Worksheet`. Don't think, like, that you want "the worksheet named WMS". Actually, `Worksheets` is a `Collection` and from among all the worksheets you want the one called WMS. Hence the plural s.

Comment: `rng.Copy ThisWorkbook.Worksheets("WMS").Cells(j, 39)` assuming the destination sheet is in the same workbook as the code.

Comment: ...also you're using `endRow` as a column index, and not using `endCol` at all

Comment: "return error" isn't a very useful description of what happens when you run your code.  What is the specific error you get?

Comment: @Variatus thanks! I just start to use VBA few days ago.. haven't got used to it but try to get used to it. I should do it from scratch..

Comment: @TimWilliams Thanks for you comment. I should have told that destination sheet is in different work book.  [rng.Copy Workbooks("File_merge.xlsm").Worksheets("WMS").Cells(j, 39)] I thought it would work but return error again..


Here is another answer: Change
Set rng = .Range(.Cells(2, 1), .Cells(2, endRow))
Set rng = .Range(.Cells(2, 1), .Cells(endRow, endCol))
Using variant array is more effective.
.rows.count  is same  with rows.count. Because the number of rows of all sheets is the same.
Private Sub btnMerge_Click()
Dim WB As Workbook
Dim WS As Worksheet: Dim toWS As Worksheet
Dim rng As Range
Dim i As Long: i = 0: Dim j As Long
Dim endCol As Long: Dim endRow As Long
Dim strWS As String
Dim Target As Range, vDB As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Me.lstWB.ListCount = 0 Then
MsgBox &quot;No file have selected&quot;
Exit Sub
End If
Set toWS = ActiveSheet
'j = toWS.Cells(toWS.Rows.Count, 1).End(xlUp).Row
For i = 0 To Me.lstWB.ListCount - 1
Set WB = Application.Workbooks.Open(Me.lstWB.List(i))
For Each WS In WB.Worksheets
With WS
endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'Set rng = .Range(.Cells(2, 1), .Cells(2, endRow))
vDB = .Range(.Cells(2, 1), .Cells(endRow, endCol))
'rng.Copy Worksheet(&quot;WMS&quot;).Cells(j, 39)
Set Target = toWS.Cells(Rows.Count, 39).End(xlUp)(2)
'j = j + rng.Rows.Count
Target.Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
End With
Next
WB.Close
MsgBox &quot;Done&quot;
Unload Me
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 6, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:09/23/20', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<excel><vba>', 'Title': "I've wanted to copy and paste specific data on existing worksheet from workbook loaded", 'ViewCount': 39, 'id': 64019308, 'user_AboutMe': None, 'user_AccountId': 12297280.0, 'user_DisplayName': 'hoegwon kim', 'user_Reputation': 3.0, 'user_Views': 5.0, 'user_id': 8972524.0}}