Could someone please help with a macro or any other suggestion to format an
Excel sheet. Basically data is downloaded from a bank every week and the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line 8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one data
set that is for the record on line 1 to 7. My question is how can I set it
to replicate so that it replicates for line 8-14 and then 15-21 and so on.
The source formatting remains the same except that the total length of the
file( no of records) changes every week.


Thanks very much for your help.

Regards

Sam

Hi
This will copy the first seven rows in columns A and B down through the
column

Sub Macro1()
Dim myRange As Range
Dim myRangeRowCount As Long
Dim i As Long

Application.ScreenUpdating = False

Set myRange = Range("A1").CurrentRegion
myRangeRowCount = myRange.Rows.Count 'a multiple of 7
Range("A1:B7").Copy
For i = 1 To myRangeRowCount \ 7 - 1
Range("A1").Offset(7 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
Next i
End Sub

Your data is assumed to start in A1. Change the "A1:B7" to your data
range in the first 7 rows.
regards
Paul

Sam Commar wrote:

Could someone please help with a macro or any other suggestion to format an
Excel sheet. Basically data is downloaded from a bank every week and the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line 8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one data
set that is for the record on line 1 to 7. My question is how can I set it
to replicate so that it replicates for line 8-14 and then 15-21 and so on.
The source formatting remains the same except that the total length of the
file( no of records) changes every week.


Thanks very much for your help.

Regards

Sam

Paul

I tried running it but get a compile error for

Range("A1").Offset(6 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False,


As my range is A1 to P6 for each record here is the macro that I created.
(Sorry each record is 6 lines and not 7)


Sub Macro1()
Dim myRange As Range
Dim myRangeRowCount As Long
Dim i As Long

Application.ScreenUpdating = False

Set myRange = Range("A1").CurrentRegion
myRangeRowCount = myRange.Rows.Count 'a multiple of 7
Range("A1:P6").Copy
For i = 1 To myRangeRowCount \ 6 - 1
Range("A1").Offset(6 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
Next i
End Sub


wrote in message
oups.com...
Hi
This will copy the first seven rows in columns A and B down through the
column

Sub Macro1()
Dim myRange As Range
Dim myRangeRowCount As Long
Dim i As Long

Application.ScreenUpdating = False

Set myRange = Range("A1").CurrentRegion
myRangeRowCount = myRange.Rows.Count 'a multiple of 7
Range("A1:B7").Copy
For i = 1 To myRangeRowCount \ 7 - 1
Range("A1").Offset(7 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
Next i
End Sub

Your data is assumed to start in A1. Change the "A1:B7" to your data
range in the first 7 rows.
regards
Paul

Sam Commar wrote:

Could someone please help with a macro or any other suggestion to format
an
Excel sheet. Basically data is downloaded from a bank every week and the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line
8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one
data
set that is for the record on line 1 to 7. My question is how can I set
it
to replicate so that it replicates for line 8-14 and then 15-21 and so
on.
The source formatting remains the same except that the total length of
the
file( no of records) changes every week.


Thanks very much for your help.

Regards

Sam

Hi
Still works for me. The only thing I can think of is that when you
break a line of code, you need to end the first line with "Space
underscore" to continue the line
e.g.
Range("A1").Offset(6 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, _

Transpose:=False

and NOT
Range("A1").Offset(6 * i, 0).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks:=False,

Transpose:=False

See if that is your problem. Alternatively, don't break the line.
regards
Paul

Sam Commar wrote:

Paul

I tried running it but get a compile error for

Range("A1").Offset(6 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False,


As my range is A1 to P6 for each record here is the macro that I created.
(Sorry each record is 6 lines and not 7)


Sub Macro1()
Dim myRange As Range
Dim myRangeRowCount As Long
Dim i As Long

Application.ScreenUpdating = False

Set myRange = Range("A1").CurrentRegion
myRangeRowCount = myRange.Rows.Count 'a multiple of 7
Range("A1:P6").Copy
For i = 1 To myRangeRowCount \ 6 - 1
Range("A1").Offset(6 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
Next i
End Sub


wrote in message
oups.com...
Hi
This will copy the first seven rows in columns A and B down through the
column

Sub Macro1()
Dim myRange As Range
Dim myRangeRowCount As Long
Dim i As Long

Application.ScreenUpdating = False

Set myRange = Range("A1").CurrentRegion
myRangeRowCount = myRange.Rows.Count 'a multiple of 7
Range("A1:B7").Copy
For i = 1 To myRangeRowCount \ 7 - 1
Range("A1").Offset(7 * i, 0).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
Next i
End Sub

Your data is assumed to start in A1. Change the "A1:B7" to your data
range in the first 7 rows.
regards
Paul

Sam Commar wrote:

Could someone please help with a macro or any other suggestion to format
an
Excel sheet. Basically data is downloaded from a bank every week and the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line
8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one
data
set that is for the record on line 1 to 7. My question is how can I set
it
to replicate so that it replicates for line 8-14 and then 15-21 and so
on.
The source formatting remains the same except that the total length of
the
file( no of records) changes every week.


Thanks very much for your help.

Regards

Sam