Merging two Spreadsheets

Microsoft Excel 2003 (full product)
January 26, 2010 at 08:56:15
Specs: Windows XP
I review our department's technology bill every month and it has become quite a pain to do the same process over and over especially since the bill can be as large as 20,000 line items. Because of how many manipulations I have to make I will break my questions up into smaller questions.

The first thing I would like to know how to do is to combine two worksheets of information. I import the bill into two seperate spreadsheets. However, I would like to have a Macro that copies all of the information on the second sheet and adds it to the end of the first sheet.

See More: Merging two Spreadsheets

Report •

January 26, 2010 at 09:56:53
Hi Logobogo

Check this PC software. You can import .csv/Excel files into it, do all sorts of data processing and export it back to Excel. Visit and go to pages Importing Files and Programming Instruction Set. Hope it can help you.


Report •

January 26, 2010 at 10:02:16
How's this...

Option Explicit
Sub AddToBottom()
Dim sheet1End
'Determine last used row in Sheet1
  sheet1End = Sheets(1).UsedRange.Rows.Count
'Copy everything from Sheet2 to the end of Sheet1
  Sheets(2).UsedRange.Copy Destination:=Sheets(1).Range("A" & sheet1End + 1)
End Sub

Report •

January 28, 2010 at 09:06:14
It worked. I do have two questions though:

1) Does the Sheets name have to be personalized? I placed the name of the worksheet in the macro and it worked, but is there a way for me to run the macro on an active workbook and for it to just take the end of one sheet and move it to another.

2) I tried to add a sort function to this macro but got an error. How can I add more VBA to this macro? Should I use a "Call"?

Report •

Related Solutions

January 28, 2010 at 09:15:14
So I just found the answer to my 2nd question. I will just tack on Call functions at the main macro.

Report •

January 28, 2010 at 11:43:48
re: is there a way for me to run the macro on an active workbook and for it to just take the end of one sheet and move it to another.

As written, the code I offered does not care about sheet names since it refers to the sheets by number (1 & 2) in the order that the sheet tabs appear

Sheets(1) is the first sheet, Sheets(2) is the second.

If Sheet1 was named Parts, you could use Sheets(1) or Sheets("Parts") to refer to that sheet. The advantage of using sheet names is that you can rearrange the order of the tabs without impacting the results of the code. You can't do that if you use sheet numbers.

By the way, you can also "calculate" the sheet reference in various ways.

- Sheets(Sheets.Count) will refer to the last sheet.

- Sheets(Sheets.Count -1) will refer to the second to last sheet.

- Sheets(Sheets(1).Range("A1").Value will refer to the sheet whose name is stored in Sheets1!A1

myNum = 1
myShtName = "Sheet" & myNum + 2

will refer to Sheet3

I could go on all day, but I think you get the picture.

Report •

January 29, 2010 at 13:11:39
Wow. Thank you so much. I didn't know that! I will continue to tweak the macro a bit to see the other capabilities.

Report •

Ask Question