Excel formatting

  • Thread starter Thread starter Sam
  • Start date Start date
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
 
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.
 
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.
 
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
 
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
 
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
 
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
 
Thanks a billion. I willtry this today with the latest bank output

Thanks again. Your time is truly apprecaited.
Thanks
 
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
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel formatting 4
Macro formatting 3
WCG Stats Saturday 29 October 2022 2
WCG Stats Monday 14 November 2022 2
WCG Stats Wednesday 30 November 2022 2
WCG Stats Friday 28 October 2022 2
WCG Stats Wednesday 26 October 2022 3
WCG Stats Monday 07 November 2022 2

Back
Top