Impossible Copying and inserting Macro....

A

Anteka

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

I recieve a daily report that gets filtered onto a spreadsheet. What I would like to do is be able to set up a macro that would take this daily filtered information and insert it into a master sheet that holds 6-8 weeks worth of work.

The issue I am having at the moment is that I do not know how to set up a macro that would be able to copy a changing amount of information. At the moment I am trying to get the macro to either Copy all the filtered information into the mastersheet either at the top or bottom of the data on the mastersheet. However I just end up either pasting over the excisting data, with blanks or it doesn't copy all the information...

hope that made sense?

Please guys this is driving my up the wall.... HELP!!!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Please post the code that you have so far. Finding the next row is easy:

Code: 
Dim NR As Long
NR = Range("A" & Rows.Count).End(xlUp).Row.Offset(1, 0)
 
Upvote 0
Well to be honest I am a complete Excel and Macro Novice... Only recorded my first macro this week...

Don't really use Visual Basic to write the code or anything...just start on the spreadsheet and record everything I do....

anywhy here is the messy non working macro....

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macro to filter report based on each change in a column and save copies
Replies
1
Views
104
sebasp
S
K
  • Solved
needing a macro to run this type of question "value in column (2 values: "s" or "l") COPIES values from the same row only column A and B info
Replies
9
Views
202
Alex Blakenburg
A
S
  • Question
Can a macro help create a user hierarchy?
Replies
7
Views
166
Akuini
Akuini
A
  • Question
VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section
Replies
1
Views
168
John_w
J
G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
268
GardianAngelUK
G

Forum statistics

Threads
1,214,784
Messages
6,121,539
Members
449,038
Latest member
Guest1337

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