?
Solved

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

Posted on 2014-01-22
4
Medium Priority
?
465 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 31

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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

764 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