Solved

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

Posted on 2014-01-22
4
454 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

920 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

14 Experts available now in Live!

Get 1:1 Help Now