Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

MS Excel - Copy data range without existing formatting.

In my vb.net application, I create a new worksheet in an existing Excel workbook by transferring data from an MS Access query.

With the following code (thanks again Harry Lee!), a range is selected in the new worksheet then copied to a different range in an old worksheet in the same workbook:

Dim NewWS As Worksheet, OldWS As Worksheet, NewRng As Range, OldLastRW As Long
NewWS = objExcel.Sheets("NewData")
OldWS = objExcel.Sheets("OldData")
OldLastRW = OldWS.Cells(OldWS.Rows.Count, 1).End(XlDirection.xlUp).Row
NewRng = NewWS.Range(NewWS.Cells(2, 1), NewWS.Cells(NewWS.Cells _(NewWS.Rows.Count, 1).End(XlDirection.xlUp).Row, 12))
NewRng.Copy(OldWS.Cells(OldLastRW + 2, 1))

All works well, but when the new worksheet was initially created from the Access query, the cells with data in them have borders which I do not want copied to the new worksheet.

What can I add/modify in the above code to prevent any formatting being copied with the data?
0
Tim313
Asked:
Tim313
1 Solution
 
unknown_routineCommented:
You can clear formats after copying:


For example

Range("C2:E4").Select
Selection.ClearFormats


So each time after you copy to destination, select the destination and then clear formats.
0
 
Ejgil HedegaardCommented:
If you have formulas to be copied with the range then the method from unknown_routine is the easiest way, but you say it is a table so probably you only need to copy values.

After copy (your last line) select the destination sheet and the upper left cell where you want the result, then insert the values with
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
0
 
Saqib Husain, SyedEngineerCommented:
Although this question is asked in the excel zone, the more appropriate zone would be vb.net

I have not worked on vb.net but this works in VBA

OldWS.Cells(OldLastRW + 2, 1).resize(NewRng.rows,NewRng.columns).value=NewRng.value
0
 
Tim313Author Commented:
To all, thanks for your suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now