Go Premium for a chance to win a PS4. Enter to Win

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

VB.Net / Excel - Determine range and append to end of another worksheet

I have vb.net code to delete a worksheet, then export an Access datatable to replace the deleted worksheet, call this "NewSheet".

Now I need to determine the range of data in "NewSheet" and append that range to the end of existing data in another worksheet "OldSheet" in the same workbook.

Both worksheets would use the same columns (say A,B.C).

The source range on "NewSheet" would start at "A2" and end at "C LastRow".

The destination range on "OldSheet" would need to start at "A LastRowOfExistingData +1".

I generally work with Access and have little experience coding for Excel. Can someone help me with this?
0
Tim313
Asked:
Tim313
  • 2
1 Solution
 
gowflowCommented:
yes for sure and to be practical can you post a sample of this workbook with fake data. Make sure the sheet names are the same as the real ones so you do not need to change the code.
Upon your posting of the file I will build a macro for you.
gowflow
0
 
Tim313Author Commented:
Thanks for your response and help.

I've attached a sample that has the fake data with the layout I explained in my original post.

I don't think I'll have a problem with making changes for names, etc.

Thanks again.
Scrap.xlsx
0
 
Harry LeeCommented:
Tim313,

You can use the following code if your sheet names are not going to change.

Sub AppendData()
Dim NewWS As Worksheet, OldWS As Worksheet, NewRng As Range, OldLastRW As Long

Set NewWS = Sheets("tblNewSheet")
Set OldWS = Sheets("OldSheet")

OldLastRW = OldWS.Cells(Rows.Count, 1).End(xlUp).Row
Set NewRng = NewWS.Range(NewWS.Cells(2, 1), NewWS.Cells(NewWS.Cells(Rows.Count, 1).End(xlUp).Row, 3))
NewRng.Copy Destination:=OldWS.Cells(OldLastRW + 1, 1)

End Sub

Open in new window


However, if your sheets' name changes every time, you will have to either make them consistent or you will have to update the
Set NewWS = Sheets("tblNewSheet"),
and
SetOldWS = Sheets("OldSheet")
to what everthey will be.
0
 
Tim313Author Commented:
Thanks for your help and code, works great!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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