Excel formatting

S

Sam

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 replciates 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
 
D

David McRitchie

Hi Sam,
I would suggest using Quicken instead of Excel.

But I think if you look at your choices for download, there
should be CSV or (Comma Separated Values) choice
available, or might even be marked as Excel.
 
S

Sam Commar

David

Thanks so much. I am looking but cant find the relevant info. Can you please
point me to the relevant info on the site.
 
J

jlepack

Post your macro and I'm sure someone (me, if I get there first) will
make it automate.

If you're ambitious, all you need is to make it lopp and jump down 8
lines and do it all again.

But again, if you gave a sample of data and the expected output, or
even just your macro, then it can be fixed up in a jiff.

Cheers,
Jason Lepack
 
S

Sam Commar

Sorry Just to guide you on the mapping of how the formatting is done please
see below:

Record Record Hardcode value if
Source Destination value not coming
from Source Data.

A1 'Document'
B1 Blank
G6 C1
F1 D1
E1 Blank
F1 Blank
G1 BLANK
H1 P1

SECOND LINE OF DESTINATION
A2 'Transaction'
B2 BLANK
G3 C2
..
D2 BLANK
E2 BLANK
G4 F2
G2 BLANK
H2 BLANK
I2 BLANK
J2 BLANK
P1 K2
L2 BLANK
G2 M2
 
S

Sam Commar

Please find attached the macro.

I want it to run for every record. My destination record set is 2 lines
while my source record set is 6 lines.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 12/7/2006 by DSC
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Document"
Range("C1").Select
Sheets("Sheet1").Select
Range("G6").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("F1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("D1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("P1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("H1").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Transaction"
Range("C2").Select
Sheets("Sheet1").Select
Range("G3").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("G4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("F2").Select
ActiveSheet.Paste
Range("H1").Select
Application.CutCopyMode = False
Selection.Copy
Range("K2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("M2").Select
ActiveSheet.Paste
Columns("K:K").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0.00"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
End Sub
 
J

jlepack

Try this:

Public Sub bankToMe()
' BankToMe() - coded by Jason Lepack on 12/07/2006
' Moves data from "sheet1" to a new sheet in the way specified by Sam
Commar
' Each record on "sheet1" is 6 lines with a space between, or 7 lines
' Each record on "output" is 2 lines with a space between

' two worksheets and a range on each worksheet
Dim wsA As Worksheet, wsB As Worksheet
Dim rA As Range, rB As Range

' initialize
Set wsA = ActiveWorkbook.Sheets("sheet1")
Set wsB = ActiveWorkbook.Sheets.Add
wsB.Name = "Output"
Set rA = wsA.Range("A1")
Set rB = wsB.Range("A1")

Application.ScreenUpdating = False

' loop until there isn't a next record
Do While Not rA.Offset(0, 5).Value = ""
rB.Value = "Document"
rB.Offset(0, 2).Value = rA.Offset(5, 6) ' G6 -> C1
rB.Offset(0, 3).Value = rA.Offset(0, 5).Value ' F1 -> D1
rB.Offset(0, 15).Value = rA.Offset(0, 7).Value ' H1 -> P1
rB.Offset(1, 0).Value = "Transaction"
rB.Offset(1, 2).Value = rA.Offset(2, 6).Value ' G3 -> C2
rB.Offset(1, 5).Value = rA.Offset(3, 6).Value ' G4 -> F2
rB.Offset(1, 10).Value = rA.Offset(0, 15).Value ' P1 -> K2
rB.Offset(1, 12).Value = rA.Offset(1, 6).Value ' G2 -> M2
Set rA = rA.Offset(7, 0) ' this is the number of lines in a
record in "sheet1"
Set rB = rB.Offset(3, 0) ' this is the number of lines in a
record in "output"
Loop
' format columns H and K as numbers
Set rB = Range("H:H,K:K")
rB.NumberFormat = "0.00"

' clean up
Set rA = Nothing
Set rB = Nothing
Set wsA = Nothing
Set wsB = Nothing
Application.ScreenUpdating = True
End Sub
 
S

Sam

Thanks a billion. I willtry this today with the latest bank output

Thanks again. Your time is truly apprecaited.
Thanks
 
J

jlepack

Make sure you reply here with how it went. There are a few questions
that you'll have that readily jump to mind, so let me know.

Cheers,
Jason Lepack
 
S

Sam Commar

Jason

I tested this and for the most part the data formatted fine however it
stopped after the first record and did not loop on.
Again every record is 6 lines.

Thanks for your help.

Sam
 
J

jlepack

So then there is no space between the records so then this should work.
Public Sub bankToMe()
' BankToMe() - coded by Jason Lepack on 12/07/2006
' Moves data from "sheet1" to a new sheet in the way specified by Sam
Commar
' Each record on "sheet1" is 6 lines with a space between, or 7 lines
' Each record on "output" is 2 lines with a space between

' two worksheets and a range on each worksheet
Dim wsA As Worksheet, wsB As Worksheet
Dim rA As Range, rB As Range

' initialize
Set wsA = ActiveWorkbook.Sheets("sheet1")
Set wsB = ActiveWorkbook.Sheets.Add
wsB.Name = "Output"
Set rA = wsA.Range("A1")
Set rB = wsB.Range("A1")

Application.ScreenUpdating = False

' loop until there isn't a next record
Do While Not rA.Offset(0, 5).Value = ""
rB.Value = "Document"
rB.Offset(0, 2).Value = rA.Offset(5, 6) ' G6 -> C1
rB.Offset(0, 3).Value = rA.Offset(0, 5).Value ' F1 -> D1
rB.Offset(0, 15).Value = rA.Offset(0, 7).Value ' H1 -> P1
rB.Offset(1, 0).Value = "Transaction"
rB.Offset(1, 2).Value = rA.Offset(2, 6).Value ' G3 -> C2
rB.Offset(1, 5).Value = rA.Offset(3, 6).Value ' G4 -> F2
rB.Offset(1, 10).Value = rA.Offset(0, 15).Value ' P1 -> K2
rB.Offset(1, 12).Value = rA.Offset(1, 6).Value ' G2 -> M2
Set rA = rA.Offset(6, 0) ' this is the number of lines in a
record in "sheet1"
Set rB = rB.Offset(3, 0) ' this is the number of lines in a
record in "output"
Loop
' format columns H and K as numbers
Set rB = Range("H:H,K:K")
rB.NumberFormat = "0.00"

' clean up
Set rA = Nothing
Set rB = Nothing
Set wsA = Nothing
Set wsB = Nothing
Application.ScreenUpdating = True
End Sub
 
Top