Hi All,

recently I have learned how to record and use macros (with a fair bit of
help from this group) and I have got them working well, except for one.

At present I click on the macro and it goes to sheet 2 and selects and
copies A2 down to end of data, then pastes it in sheet 1 from A6 down.
Next it goes back to Sheet 2 and selects C2 accross to end of data and then
down to end of data and copies it and pastes it into sheet 1 from C6 down to
bottom.
There are about 1800 rows and it takes a while to do. I must be impatient.
On Sheet 1 column B is vlookup for other relevant data. I am not sure that
this makes a difference.

This macro works however is there a better way to do this easier or maybe
faster?

Thanks
Kevin

Post the code that you are using. If this has been produced by
recording keystrokes, then there are probably many selections made,
and these can often be improved on.

Pete

On Sep 30, 11:22*pm, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
> Hi All,
>
> recently I have learned how to record and use *macros (with a fair bit of
> help from this group) and I have got them working well, except for one.
>
> At present I click on the macro and it goes to sheet 2 and selects and
> copies A2 down to end of data, then pastes it in sheet 1 from A6 down.
> Next it goes back to Sheet 2 and selects *C2 accross to end of data andthen
> down to end of data and copies it and pastes it into sheet 1 from C6 downto
> bottom.
> There are about 1800 rows and it takes a while to do. I must be impatient..
> On Sheet 1 column B is vlookup for other relevant data. I am not sure that
> this makes a difference.
>
> This macro works however is there a better way to do this easier or maybe
> faster?
>
> Thanks
> Kevin

Here it is Pete


Sheets("Raw data").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A6").Select
ActiveSheet.Paste
Sheets("Raw data").Select
Range("C2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2").Select
Sheets("Sheet1").Select
Range("C6").Select
ActiveSheet.Paste
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("B8").Select
Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=
_
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010", Operator:=xlAnd
End Sub


"Pete_UK" <(E-Mail Removed)> wrote in message
news:b9c53923-1125-456a-8b2f-(E-Mail Removed)...
Post the code that you are using. If this has been produced by
recording keystrokes, then there are probably many selections made,
and these can often be improved on.

Pete

On Sep 30, 11:22 pm, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
> Hi All,
>
> recently I have learned how to record and use macros (with a fair bit of
> help from this group) and I have got them working well, except for one.
>
> At present I click on the macro and it goes to sheet 2 and selects and
> copies A2 down to end of data, then pastes it in sheet 1 from A6 down.
> Next it goes back to Sheet 2 and selects C2 accross to end of data and
> then
> down to end of data and copies it and pastes it into sheet 1 from C6 down
> to
> bottom.
> There are about 1800 rows and it takes a while to do. I must be impatient.
> On Sheet 1 column B is vlookup for other relevant data. I am not sure that
> this makes a difference.
>
> This macro works however is there a better way to do this easier or maybe
> faster?
>
> Thanks
> Kevin

On Oct 1, 4:48*am, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
> Here it is Pete
>
> * * Sheets("Raw data").Select
> * * Range("A2").Select
> * * Range(Selection, Selection.End(xlDown)).Select
> * * Selection.Copy
> * * Sheets("Sheet1").Select
> * * Range("A6").Select
> * * ActiveSheet.Paste
> * * Sheets("Raw data").Select
> * * Range("C2").Select
> * * Range(Selection, Selection.End(xlToRight)).Select
> * * Range(Selection, Selection.End(xlDown)).Select
> * * Application.CutCopyMode = False
> * * Selection.Copy
> * * ActiveWindow.ScrollColumn = 2
> * * ActiveWindow.ScrollColumn = 1
> * * Range("A2").Select
> * * Sheets("Sheet1").Select
> * * Range("C6").Select
> * * ActiveSheet.Paste
> * * Range("A6").Select
> * * Range(Selection, Selection.End(xlToRight)).Select
> * * Range(Selection, Selection.End(xlDown)).Select
> * * Application.CutCopyMode = False
> * * Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> * * Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> * * With Selection.Borders(xlEdgeLeft)
> * * * * .LineStyle = xlContinuous
> * * * * .Weight = xlThin
> * * * * .ColorIndex = xlAutomatic
> * * End With
> * * With Selection.Borders(xlEdgeTop)
> * * * * .LineStyle = xlContinuous
> * * * * .Weight = xlThin
> * * * * .ColorIndex = xlAutomatic
> * * End With
> * * With Selection.Borders(xlEdgeBottom)
> * * * * .LineStyle = xlContinuous
> * * * * .Weight = xlThin
> * * * * .ColorIndex = xlAutomatic
> * * End With
> * * With Selection.Borders(xlEdgeRight)
> * * * * .LineStyle = xlContinuous
> * * * * .Weight = xlThin
> * * * * .ColorIndex = xlAutomatic
> * * End With
> * * With Selection.Borders(xlInsideVertical)
> * * * * .LineStyle = xlContinuous
> * * * * .Weight = xlThin
> * * * * .ColorIndex = xlAutomatic
> * * End With
> * * With Selection.Borders(xlInsideHorizontal)
> * * * * .LineStyle = xlContinuous
> * * * * .Weight = xlThin
> * * * * .ColorIndex = xlAutomatic
> * * End With
> * * Range("B8").Select
> * * Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=
> _
> * * * * xlGuess, OrderCustom:=1, MatchCase:=False,
> Orientation:=xlTopToBottom, _
> * * * * DataOption1:=xlSortNormal
> * * Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010", Operator:=xlAnd
> End Sub
>
> "Pete_UK" <pashu...@auditel.net> wrote in message
>
> news:b9c53923-1125-456a-8b2f-(E-Mail Removed)...
> Post the code that you are using. If this has been produced by
> recording keystrokes, then there are probably many selections made,
> and these can often be improved on.
>
> Pete
>
> On Sep 30, 11:22 pm, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
>
>
>
> > Hi All,
>
> > recently I have learned how to record and use macros (with a fair bit of
> > help from this group) and I have got them working well, except for one.
>
> > At present I click on the macro and it goes to sheet 2 and selects and
> > copies A2 down to end of data, then pastes it in sheet 1 from A6 down.
> > Next it goes back to Sheet 2 and selects C2 accross to end of data and
> > then
> > down to end of data and copies it and pastes it into sheet 1 from C6 down
> > to
> > bottom.
> > There are about 1800 rows and it takes a while to do. I must be impatient.
> > On Sheet 1 column B is vlookup for other relevant data. I am not sure that
> > this makes a difference.
>
> > This macro works however is there a better way to do this easier or maybe
> > faster?
>
> > Thanks
> > Kevin- Hide quoted text -
>
> - Show quoted text -

try this
with Sheets("Raw data")
.Range("a2", Range("a2").End(xlDown)).copy
Sheets("Sheet1").Range("A6")
.Range("c2", Range("a2").End(xlDown).End(xlToRight)).copy
Sheets("Sheet1").Range("C6")
.Range("a6",
Range("a6").End(xlDown).End(xlToRight)).borders.linestyle=xlContinuous
.Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010",
Operator:=xlAnd
end with
End Sub

On Oct 1, 8:04*am, Don Guillett Excel MVP <dguille...@austin.rr.com>
wrote:
> On Oct 1, 4:48*am, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
>
>
>
>
>
> > Here it is Pete
>
> > * * Sheets("Raw data").Select
> > * * Range("A2").Select
> > * * Range(Selection, Selection.End(xlDown)).Select
> > * * Selection.Copy
> > * * Sheets("Sheet1").Select
> > * * Range("A6").Select
> > * * ActiveSheet.Paste
> > * * Sheets("Raw data").Select
> > * * Range("C2").Select
> > * * Range(Selection, Selection.End(xlToRight)).Select
> > * * Range(Selection, Selection.End(xlDown)).Select
> > * * Application.CutCopyMode = False
> > * * Selection.Copy
> > * * ActiveWindow.ScrollColumn = 2
> > * * ActiveWindow.ScrollColumn = 1
> > * * Range("A2").Select
> > * * Sheets("Sheet1").Select
> > * * Range("C6").Select
> > * * ActiveSheet.Paste
> > * * Range("A6").Select
> > * * Range(Selection, Selection.End(xlToRight)).Select
> > * * Range(Selection, Selection.End(xlDown)).Select
> > * * Application.CutCopyMode = False
> > * * Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> > * * Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> > * * With Selection.Borders(xlEdgeLeft)
> > * * * * .LineStyle = xlContinuous
> > * * * * .Weight = xlThin
> > * * * * .ColorIndex = xlAutomatic
> > * * End With
> > * * With Selection.Borders(xlEdgeTop)
> > * * * * .LineStyle = xlContinuous
> > * * * * .Weight = xlThin
> > * * * * .ColorIndex = xlAutomatic
> > * * End With
> > * * With Selection.Borders(xlEdgeBottom)
> > * * * * .LineStyle = xlContinuous
> > * * * * .Weight = xlThin
> > * * * * .ColorIndex = xlAutomatic
> > * * End With
> > * * With Selection.Borders(xlEdgeRight)
> > * * * * .LineStyle = xlContinuous
> > * * * * .Weight = xlThin
> > * * * * .ColorIndex = xlAutomatic
> > * * End With
> > * * With Selection.Borders(xlInsideVertical)
> > * * * * .LineStyle = xlContinuous
> > * * * * .Weight = xlThin
> > * * * * .ColorIndex = xlAutomatic
> > * * End With
> > * * With Selection.Borders(xlInsideHorizontal)
> > * * * * .LineStyle = xlContinuous
> > * * * * .Weight = xlThin
> > * * * * .ColorIndex = xlAutomatic
> > * * End With
> > * * Range("B8").Select
> > * * Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=
> > _
> > * * * * xlGuess, OrderCustom:=1, MatchCase:=False,
> > Orientation:=xlTopToBottom, _
> > * * * * DataOption1:=xlSortNormal
> > * * Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010", Operator:=xlAnd
> > End Sub
>
> > "Pete_UK" <pashu...@auditel.net> wrote in message
>
> >news:b9c53923-1125-456a-8b2f-(E-Mail Removed)....
> > Post the code that you are using. If this has been produced by
> > recording keystrokes, then there are probably many selections made,
> > and these can often be improved on.
>
> > Pete
>
> > On Sep 30, 11:22 pm, "Roger Dodger" <gtsmonaro...@bigpond.com> wrote:
>
> > > Hi All,
>
> > > recently I have learned how to record and use macros (with a fair bitof
> > > help from this group) and I have got them working well, except for one.
>
> > > At present I click on the macro and it goes to sheet 2 and selects and
> > > copies A2 down to end of data, then pastes it in sheet 1 from A6 down..
> > > Next it goes back to Sheet 2 and selects C2 accross to end of data and
> > > then
> > > down to end of data and copies it and pastes it into sheet 1 from C6 down
> > > to
> > > bottom.
> > > There are about 1800 rows and it takes a while to do. I must be impatient.
> > > On Sheet 1 column B is vlookup for other relevant data. I am not surethat
> > > this makes a difference.
>
> > > This macro works however is there a better way to do this easier or maybe
> > > faster?
>
> > > Thanks
> > > Kevin- Hide quoted text -
>
> > - Show quoted text -
>
> try this
> with Sheets("Raw data")
> * *.Range("a2", Range("a2").End(xlDown)).copy
> Sheets("Sheet1").Range("A6")
> * *.Range("c2", Range("a2").End(xlDown).End(xlToRight)).copy
> Sheets("Sheet1").Range("C6")
> * *.Range("a6",
> Range("a6").End(xlDown).End(xlToRight)).borders.linestyle=xlContinuous
> * *.Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending,
> Header:= *_
> * * * xlGuess, OrderCustom:=1, MatchCase:=False,
> Orientation:=xlTopToBottom, _
> * * Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010",
> Operator:=xlAnd
> end with
> End Sub- Hide quoted text -
>
> - Show quoted text -

Be sure to correct for word wrap

> with Sheets("Raw data")
> .Range("a2", Range("a2").End(xlDown)).copy
> Sheets("Sheet1").Range("A6")
> .Range("c2", Range("a2").End(xlDown).End(xlToRight)).copy
> Sheets("Sheet1").Range("C6")
> .Range("a6",

I get a compile error Here

> Range("a6").End(xlDown).End(xlToRight)).borders.linestyle=xlContinuous
> .Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending,
> Header:= _
> xlGuess, OrderCustom:=1, MatchCase:=False,
> Orientation:=xlTopToBottom, _
> Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010",
> Operator:=xlAnd
> end with
> End Sub- Hide quoted text -
>
> - Show quoted text -

There are some Range objects that need a leading dot:

with Sheets("Raw data")
.Range("a2", .Range("a2").End(xlDown)).copy
Sheets("Sheet1").Range("A6")

.Range("c2", .Range("a2").End(xlDown).End(xlToRight)).copy
Sheets("Sheet1").Range("C6")

.Range("a6", _
.Range("a6").End(xlDown).End(xlToRight)).borders.linestyle=xlContinuous

.Range("A5:N1799").Sort Key1:=.Range("B5"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _

'This is working on the selection -- which may not be what you
'want!
Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010",

end with
End Sub


On 10/02/2010 17:10, Roger Dodger wrote:
>> with Sheets("Raw data")
>> .Range("a2", Range("a2").End(xlDown)).copy
>> Sheets("Sheet1").Range("A6")
>> .Range("c2", Range("a2").End(xlDown).End(xlToRight)).copy
>> Sheets("Sheet1").Range("C6")
>> .Range("a6",
>
> I get a compile error Here
>
>> Range("a6").End(xlDown).End(xlToRight)).borders.linestyle=xlContinuous
>> .Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending,
>> Header:= _
>> xlGuess, OrderCustom:=1, MatchCase:=False,
>> Orientation:=xlTopToBottom, _
>> Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010",
>> Operator:=xlAnd
>> end with
>> End Sub- Hide quoted text -
>>
>> - Show quoted text -
>
>
>

--
Dave Peterson

On Oct 2, 9:40*pm, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> There are some Range objects that need a leading dot:
>
> with Sheets("Raw data")
> * * .Range("a2", .Range("a2").End(xlDown)).copy
> * * * * Sheets("Sheet1").Range("A6")
>
> * * .Range("c2", .Range("a2").End(xlDown).End(xlToRight)).copy
> * * * * Sheets("Sheet1").Range("C6")
>
> * * .Range("a6", _
> * * * *.Range("a6").End(xlDown).End(xlToRight)).borders.linestyle=xlContinuous
>
> * * .Range("A5:N1799").Sort Key1:=.Range("B5"), Order1:=xlAscending, _
> * * * *Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
> * * * *Orientation:=xlTopToBottom, _
>
> * * *'This is working on the selection -- which may not be what you
> * * *'want!
> * * *Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010",
>
> end with
> End Sub
>
> On 10/02/2010 17:10, Roger Dodger wrote:
>
>
>
>
>
> >> with Sheets("Raw data")
> >> .Range("a2", Range("a2").End(xlDown)).copy
> >> Sheets("Sheet1").Range("A6")
> >> .Range("c2", Range("a2").End(xlDown).End(xlToRight)).copy
> >> Sheets("Sheet1").Range("C6")
> >> .Range("a6",
>
> > I get a compile error Here
>
> >> Range("a6").End(xlDown).End(xlToRight)).borders.linestyle=xlContinuous
> >> .Range("A5:N1799").Sort Key1:=Range("B5"), Order1:=xlAscending,
> >> Header:= _
> >> xlGuess, OrderCustom:=1, MatchCase:=False,
> >> Orientation:=xlTopToBottom, _
> >> Selection.AutoFilter Field:=3, Criteria1:=">4-Aug-2010",
> >> Operator:=xlAnd
> >> end with
> >> End Sub- Hide quoted text -
>
> >> - Show quoted text -
>
> --
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Also missing line continuation. Surely your vba editor is showing in
red...
If you still have problems send me your file to (E-Mail Removed)
and this msg.