Solved

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

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

840 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