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

A

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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
Worksheets("Sheet1").Range("A4:A100").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Sheet2").Range("A" & Rows.Count + 4).Offset(0, 1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
157
Coyotex3
C
VinceF
  • Solved
VBA to Copy paste value based on value in cell
Replies
4
Views
309
VinceF
VinceF
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
676
cstuder
C
K
  • Solved
How to copy data from single cells to merge cells and vice versa
2 3
Replies
24
Views
433
Alex Blakenburg
A
S
  • Solved
Trying to copy another column from one table to another.
Replies
2
Views
389
Alex Blakenburg
A

Forum statistics

Threads
1,214,830
Messages
6,121,831
Members
449,051
Latest member
excelquestion515

Share this page

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