Solved

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

Posted on 2014-01-22
4
451 Views
Last Modified: 2014-01-23
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
Comment
Question by:Tim313
  • 2
4 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39802321
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
 

Author Comment

by:Tim313
ID: 39802796
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
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39805018
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
 

Author Closing Comment

by:Tim313
ID: 39805315
Thanks for your help and code, works great!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now