Append Macro (excel)

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
I have multiple workbooks that I need to open, format, and lastly appened into a master workbook. I have macros set up to open the .xls files, and format them. Is there a way to write an append function into a macro. All I am looking to do is select all the data on the worksheet and copy it into the master sheet. The problem i run into is each month the file changes (amount of rows) so i cant just copy and paste to the same cell. If it is not possible to do in excel, is there a way to do this into a .txt file like notepad. I have about 50 sheets and it takes to long to do this by hand....and i am lazy....any help would be greatly appreciated
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Without seeing your code, I can only make suggestions. It's a bit easier if you know how many columns you're working with but the same principle can be applied even if you don't.

When you go to paste the data, find the last row where data exists. There are several ways to do this:

Dim lastRow As Long
Dim lastRowE As Long

lastRow = Cells.Find("*", searchdirection:=xlPrevious).Row
lastRowE = Range("E:E").Find("*", searchdirection:=xlPrevious).Row
lastRowE = Range("E" & Rows.Count).End(xlUp).Row

Use the Offset Property to go down another row or two, and paste there.

Hope that helps!
 
Upvote 0
thanks for the help....i am not going to know if it works until tomorrow....i dont have it in front of me now.....i also have another questions if you can help. In the macro it opens a file in a particular folder. I have about 50 files however some months we might not have a certain file. the marco will error out if it does not find the particular file it is looking for. what would be the way to write that into the code. if the file dont not exist then "end sub". thanks again i will let you know how it works tomorrow
 
Upvote 0
the marco will error out if it does not find the particular file it is looking for. what would be the way to write that into the code. if the file dont not exist then "end sub".
Kinda' generic, but you can add "On Error Resume Next" to the code (prior to the potential error point) if you suspect an error may occur.

But like Todd said, posting some specifics (like your code) would help.

Smitty
 
Upvote 0
This is the code that I have so far. I have a couple of issues I think. First I am not sure how to use the "LastRow = Cells.Find("*", serchdirection:=xlPrevious).Row". would the search direction just be up, down, left, right? Also this worksheet that I am copying the the data from is more like a report. My issue with that is I need this code to work on all the files (around 50) and i am worried that if i use:

Dim LastRow As Long
Dim LastRowE As Long
LastRow = Cells.Find("*", serchdirection:=xlPrevious).Row
LastRowE = Range("E:E").Find("*", serchdirection:=xlPrevious).Row
LastRowE = Range("E" & Rows.Count).End(xlUp).Row


It is just going to find the first space. If this is the case it is not going to work because there are spaces all over the place. I cant figure out how to post an image. If you let me know how to do that then I will post an image of the file.



Dim LastRow As Long
Dim LastRowE As Long

Sheets("AL").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Workbooks.Open Filename:= _
"C:\Documents and Settings\bschulze\My Documents\D\Projects\F\V_Reports\V_Files\Example_Data.xls"
Cells.Select
Selection.Copy
Windows("Tester.xls").Activate
Sheets("AL").Select
Range("A1").Select
LastRow = Cells.Find("*", serchdirection:=xlPrevious).Row
LastRowE = Range("E:E").Find("*", serchdirection:=xlPrevious).Row
LastRowE = Range("E" & Rows.Count).End(xlUp).Row
ActiveSheet.Paste
Windows("Example_Data.xls").Activate
Application.CutCopyMode = False
ActiveWindow.Close
Windows("Tester.xls").Activate
Cells.Select
Cells.EntireColumn.AutoFit
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=*sub-total*", Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="=*vvvvvvv*", Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
Selection.AutoFilter
Range("A1").Select
End Sub
 
Upvote 0
Ok here it is

 
Upvote 0
let try it again
Example_Data.xls
ABCDEFGHIJ
1tttttttttttttttttttt11/4/2005##########
2AuditDetailbyTaxable
3
4
5State:ByTaxTypeDetailDivision:None
6FilingPeriod:BothDepartment:None
7
8TransactionDepartmentLocationDataSourceStateCountyCityDistrict
9DateCodeTaxableTaxableTaxableTaxable
10FilingPeriod:
11Jurisdiction:
1210/15/2005367Manual0.660.660.66
13SpanishFortE.ShoreCentre(BALDWIN)Sub-Total:47,550.42
14Jurisdiction:
1510/15/200571Manual80,506.0080,506.0080,506.00
1610/15/200571Manual515.03515.03515.03
17Tuscaloosa(TUSCALOOSA)Sub-Total:81,021.03
18September,2005Sub-Total:777,187.19
19ReportTotal:777,187.19
20vvvvvvvvvvPage1of1
21
22
Example_Data.xls
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top