Loop which copy values from many sheets

[typical_lion]

Hello,

I'm trying to create a macro which will copy values from cells E6:I6 to E27:I27 and then paste special to first empty cell in A column (sheet "mystats").

I will add one sheet to my workbook daily.

I tried to do it myself but I'm total newbie in VBA and so far I've got this:

Sub Makro3()


Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "Macro" And Not ws.Name Like "Wzór" And Not ws.Name Like "Bacground" And Not ws.Name Like "mystats" Then
Range("E6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("mystats").Select
Range("a10000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



End If

Next


End Sub

I will appreciate any help .

Regards.

[AB33]

I have not tested it, but try it

Please Login or Register  to view this content.

[typical_lion]

OMG I could hug you .

I tried to change something for better results so i added:

Sub Makro3()

Dim ws As Worksheet, LR As Long

For Each ws In ThisWorkbook.Worksheets
With ws
If Not .Name Like "Macro" And Not .Name Like "Wzór" And Not .Name Like "Background" And Not .Name Like "mystats" Then
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("E6:J6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("mystats").Select
Range("a10000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

End With
Next



End Sub

But now i got run time error 1004 (something connected to "range method".

But still... thanks a lot.

[AB33]

If the error is on the code I have provided you , Let me know in which line do you get the error.

[typical_lion]

Your code is error free.

My changes are causing it.

I will leave it as you wrote it .

[AB33]

Please Login or Register  to view this content.

This line is more flexible than select and fixed range

Please Login or Register  to view this content.

[AB33]

You can easily change this line

Please Login or Register  to view this content.

In to

Please Login or Register  to view this content.

[typical_lion]

Yeah, I know but the "& LR" copy also cells with sumed value below my chart.

I tried to avoid having sumed values in "mystats" by adding

Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

But it's not that big deal.

[AB33]

Okay, I see your point. In this case change this line

Please Login or Register  to view this content.

In to Fixed Range

Please Login or Register  to view this content.