Append data to the last row of a sheet

[JoeExcelHelp]

Hi Everyone,

Trying to append data from the 'missing_names' sheet to the last row in the WB being saved as a csv. I inserted the following line but no its not working

Thank You

ThisWorkbook.Sheets("missing_names").Range("A2:K20").Copy Sheets("WScript.Shell").Range("A" & Rows.Count).End(xlUp).Offset(1)

Sub generate_csv()
Dim Path As String
Dim wbk As Workbook
Dim wsh As Worksheet

Set wbk = Workbooks.Add(xlWBATWorksheet)
Set wsh = wbk.Worksheets(1)

ThisWorkbook.Sheets("missing_names").Range("A2:K20").Copy Sheets("WScript.Shell").Range("A" & Rows.Count).End(xlUp).Offset(1)
ThisWorkbook.Sheets("import_csv").UsedRange.Copy
wsh.Range("A1").PasteSpecial Paste:=xlPasteValues
wsh.Range("A1").PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False
Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
wbk.SaveAs Path & "rosterimport_BOS_" & Format(Now, "yyyymmddhhmmss") & ".csv", FileFormat:=xlCSV
'FileName:="H:\My Drive\KML\TOTEliteMembers\NQDev.csv", FileFormat:=xlCSV
wbk.Close True
End Sub

[HansV]

In what way is it not working?

[DocAElstein]

I inserted the following line but no its not working

Code: Select all

ThisWorkbook.Sheets("missing_names").Range("A2:K20").Copy Sheets("WScript.Shell").Range("A" & Rows.Count).End(xlUp).Offset(1)

Just curious - do you really have a worksheet with a tab name of WScript.Shell ?

[JoeExcelHelp]

No I do not.. the sheet being saved is called 'import_csv' I just wasnt certain how that worked within the WB being saved

[JoeExcelHelp]

The idea is to append the data from 'import_csv' to the WB being saved but not in the actual sheet (import_csv)

[DocAElstein]

Another way to write that code line is

Code: Select all

ThisWorkbook.Sheets("missing_names").Range("A2:K20").Copy Destination:=Sheets("WScript.Shell").Range("A" & Rows.Count).End(xlUp).Offset(1)

What that code line does is copy the range given here,
ThisWorkbook.Sheets("missing_names").Range("A2:K20")
, and then it pastes it starting top left from where you tell it to, which is here
Sheets("WScript.Shell").Range("A" & Rows.Count).End(xlUp).Offset(1)

So that last bit, Sheets("WScript.Shell").Range("A" & Rows.Count).End(xlUp).Offset(1) , needs to be a valid cell referrence, which it wont be if you have no worksheet called WScript.Shell

_.____________

You cant really save a workbook as a CSV file. It may look like that sometimes, but that is just because Excel lets you play around with CSV files in and with Excel. If you save something from Excel as a CSV file, then what actually happens is that just the data from one worksheet is put into a text file. (There is not much difference between a .csv file and a .txt file. They are just text, no worksheets, no formatting). The rows become lines of text. The columns will be replaced by a character called the seperator, - it will typically be a comma or a semicolon, depending on what language settings you have. If you are lucky, when you open a CSV file with Excel, then Excel geuses correctly what you have, and it puts each line of text on a different row, and does not show the seperator, but uses that to put the data into columns. In other words, the seperator is like a substitute for the column grid wall that you see in Excel. A text file does not have cells. A line of text is easilly recognised as like a row. But without using a seperator we would not know which column to put data in.

[HansV]

I'm sorry, you've lost me.

[DocAElstein]

who, me, or Joe?

I tell you what, though, he gave me an idea. How's this for worhsheet name

Groovy Sheet name.JPG

[DocAElstein]

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

[JoeExcelHelp]

Guys apologies for the confusion

I wasnt aware a 2nd 'ThisWorkbook.WorkSheets' ref was needed on that line. The code works perfect

Doc and Hans as awlays TY

[DocAElstein]

It might not always need the extra referrence, but it is good practice to get in the habit of always referrencing things fully, at least intially. Usually best to get into bad habits much later, once you have a much better idea about everything.