Copy values not formulas BUT keep leading zeros

Question

I want to copy a worksheet from a closed book (working ok) I want to copy the only values and not formula to my active workbook so that when I save the workbook as .csv I don't get rows of commas (,,,) at the end due to the original worksheet having formula or something else in those cells.

I've tried:

Set sourceBook = Workbooks.Open(fileName)

  sourceBook.Sheets("Access Request Spreadsheet").value.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).value
  sourceBook.Close`

But that throws an Error. The range to copy could always be different...but is there an easy way to only copy values OR when I save as .csv to strip off the rows of ,,,,, ,,,, ,,,, ,,,,

Thanks!

---Update----

Ok I was able to strip formulas in my copy and and now when I save as .csv it doesn't have extra rows of ,,,, at the end.

problem is now it's cutting leading zeros from my numbers in a column. Numbers may not be consistent in length so cant set to say "0000000".

My code is:

 Set sourceBook = Workbooks.Open(fileName)
 With ThisWorkbook
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
  End With

  With sourceBook.Sheets("Access Request Spreadsheet").UsedRange
 ThisWorkbook.Sheets("temp").Range("A1").Resize( _
    .Rows.Count, .Columns.Count) = .value
 End With
sourceBook.Close

I've tried .value.NumberFormat = "@" Any ideas?

My output is truncating my Student ID's which should be

001234

05432

1284

to:

1234

5432

1284

As mentioned, the problem is different schools have different ID field lengths so can't set it to a predefined length.

Can I keep the values as text when copying to new worksheet AND remove formulas?

I've tried:

  With sourceBook.Sheets("Access Request Spreadsheet").UsedRange

  ThisWorkbook.Sheets("temp").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .value.NumberFormat = "@"
  End With

but it throws errors :(

Cheers!

You could try With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) .Value = .Value End With — TheJeebo
Thanks that did help! Now I'm facing an issue in that the copying values only is striping leading zero's. Is there a way to copy values or as text and leave leading zeros and not have formulas seen as nul or ,,, by cvs? (I edited my post above to help show what I mean.) — Mr80s

T.M. T.M. · Accepted Answer · 2019-08-30T16:02:12

Just execute [1] formatting and [2] copying separately. .value.NumberFormat = "@" is no valid VBA syntax; you have to define the target range's number format first :-)

' [1] format as text  
  ThisWorkbook.Worksheets("temp").Range("A1").Resize( _
        .Rows.Count, .Columns.Count).NumberFormat = "@"
' [2] copy values
  ThisWorkbook.Worksheets("temp").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .Value

Copy values not formulas BUT keep leading zeros

1 Answers