You perhaps just need to be a bit more careful and precise about from where and to where stuff is copied.
You don't copy from a worksheet to a workbook. You copy from a range in a worksheet in a workbook, to a range in a worksheet in a workbook.
To avoid mistakes, don't rely on Excel geussing what workbook it tries to find the worksheet you want to refer to.
In your case, always use either
ThisWorkbook.Worksheets......
or
wbk.Worksheets.......
If you just use
Worksheets.... or
Sheets.... then Excel is left to geuss what workbook you are talking about
_.______________________
Possibly the only thing you did wrong was that this
Code: Select all
ThisWorkbook.Sheets("missing_names").Range("A2:K20").Copy Sheets("WScript.Shell").Range("A" & Rows.Count).End(xlUp).Offset(1)
maybe shoukd have been
Code: Select all
ThisWorkbook.Sheets("missing_names").Range("A2:K20").Copy ThisWorkbook.WorkSheets("import_csv").Range("A" & Rows.Count).End(xlUp).Offset(1)
Personally I might write it like
Code: Select all
ThisWorkbook.WorkSheets("missing_names").Range("A2:K20").Copy Destination:=ThisWorkbook.WorkSheets("import_csv").Range("A" & Rows.Count).End(xlUp).Offset(1)
Just a personal preferrence, to remind me what is going on. ( I might also define another variable for
ThisWorkbook, and thereafter use that instead of
ThisWorkbook )
_.________________________
This, in words , is what is going on in this code line
Code: Select all
ThisWorkbook.WorkSheets("missing_names").Range("A2:K20").Copy Destination:=ThisWorkbook.WorkSheets("import_csv").Range("A" & Rows.Count).End(xlUp).Offset(1)
.........
The data in range A2:K20 in the worksheet "missing_names" , in the workbook in which the coding is
, is being appended to the data in worksheet "import_csv", in the workbook in which the coding is
or in other words,
.........
The data in range A2:K20 in the worksheet "missing_names" , in the workbook in which the coding is
, is being pasted in starting from the next free row in worksheet "import_csv", in the workbook in which the coding is
If that is not what you want, then change the referrences appropriately
_._______________________________________________________________________
It might help to write that last code line out in a slightly different form. This is basically the same , or very similar, just written a bit more explicitly
Code: Select all
ThisWorkbook.WorkSheets("missing_names").Range("A2:K20").Copy
ThisWorkbook.WorkSheets("import_csv").Paste Destination:=ThisWorkbook.WorkSheets("import_csv").Range("A" & Rows.Count).End(xlUp).Offset(1)
That is using the worksheets Paste method. You can see it has a similar form to another part of your coding
Code: Select all
ThisWorkbook.Sheets("import_csv").UsedRange.Copy
wsh.Range("A1").PasteSpecial Paste:=xlPasteValues
wsh.Range("A1").PasteSpecial Paste:=xlPasteFormats
That bit of your coding is doing something similar. It is using the Range PasteSpecial method. The Range PasteSpecial method allows you to decide fairly precisely what formating you want to copy.
The experts argue a bit about what is going on in the worksheets Paste method and in the code line that you are using. It is a bit less precise and less predictable on what formats get copied