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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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

K
  • Question Question
Prevent others from unhiding columns, but still being able to insert and copy rows
Replies
4
Views
285
keykan
K
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
357
Anthony47
A
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
715
Micron
M
L
  • Question Question
Macro to paste values data from GetPivotData in a separate workbook
Replies
0
Views
118
LiteBrown
L
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
883
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

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