Help! Having Issue with Copying of information with a Macro...

Anteka

New Member
Joined
Aug 17, 2008
Messages
5
Hi Guys...

I really need some help... I receive a daily report as a txt file. Then I use:

ChDir "C:\Users\Mon&Ant\Desktop"
Workbooks.OpenText Filename:="C:\Users\Mon&Ant\Desktop\0.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), _
TrailingMinusNumbers:=True


To put it into a Excel file. From there I can Autofilter in into the different information I need. However I now wish to copy this filtered information into a Master sheet and insert it so that that I can keep 6-8 weeks of information. So I have now tried to create a macro that would enable me to do this so that I do not need to do this manually everyday. The problem I'm finding is that I can't get the macro set up so that it copies all the visible filtered information daily. (as the amount of information changes daily, my macro tends to either not copy all the information or I have blank spaces in my work... which I can't auto filter then on the Master sheet....)

I am a Novice at using excel and have only used my first macro this week... So I am really struggeling here... Please help!

Not sure if that made anysense however I will attact my mess of a macro for you to have a look at and please contact me ASAP with any questions and/or help...

Thanks

Anton


Here is my mess:

Sub RetractsToMaster()
'
' RetractsToMaster Macro
'
'
ChDir "C:\Users\Mon&Ant\Desktop"
Workbooks.OpenText Filename:="C:\Users\Mon&Ant\Desktop\0.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), _
TrailingMinusNumbers:=True
Columns("A:A").ColumnWidth = 11.29
Range("E:E,G:G,H:H,I:I,L:L,M:M").Select
Range("M1").Activate
Selection.Delete Shift:=xlToLeft
Range("A1:H492").Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\Mon&Ant\Desktop\Master Retract Report.xls"
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=160200" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H20").Select
Selection.Cut
Sheets("160200").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=160201" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H162").Select
Selection.Cut
Sheets("160201").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveWindow.ScrollRow = 386
ActiveWindow.ScrollRow = 384
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 360
ActiveWindow.ScrollRow = 358
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 331
ActiveWindow.ScrollRow = 326
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 311
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 287
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 157
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=602465" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H45").Select
Selection.Cut
Sheets("602465").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 1
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=831790" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H10").Select
Selection.Cut
Sheets("831790").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=831791" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H17").Select
Selection.Cut
Sheets("831791").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=983020" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H12").Select
Selection.Cut
Sheets("983020").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=988550" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H6").Select
Selection.Cut
Sheets("988550").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7, Criteria1:="=988555" _
, Operator:=xlAnd
Range("A1:H493").Select
Selection.Copy
Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1:H44").Select
Selection.Cut
Sheets("988555").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Start").Select
ActiveWindow.SmallScroll Down:=-42
ActiveSheet.Range("$A$1:$H$493").AutoFilter Field:=7
Range("A1:H493").Select
Selection.ClearContents
Sheets("Start").Select
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
End Sub
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
I would suggest looking at Advanced Filtering to do what you are after.
When you want to copy filtered data to another location, advanced filter is the best way to go.

Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You can clean-up recorded macro code:

For: "ActiveWindow.ScrollRow = 386" the highest count is the only one needed!


Refrences like:
"Range("A1:H493").Select
Selection.Copy"

are the same as: Range("A1:H493").Copy

And:

"Sheets("Work").Select
Range("A1").Select
ActiveSheet.Paste"

are the same as:
Sheets("Work").Range("A1").Paste
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Worksheets("Sheet1").Range("A4:A100").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Sheet2").Range("A" & Rows.Count + 4).Offset(0, 1)
 

Forum statistics

Threads
1,081,802
Messages
5,361,388
Members
400,628
Latest member
ganeshkhatri

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top