PDA

View Full Version : Solved: help with the first program



maruduran
07-15-2012, 10:35 AM
I have attempted a small vba program which is annexed. I have a data sheet which is in the Sheet1 in the annexed file. I want an output for each asset showing the name of the sheet as the description of the asset and giving some details of the company with opening balance value and depreciation and closing balance.This is just the beginning. If there are 100 assets there would be 100 sheets. The output range in the sheets would be resized as to format as per the length of the description etc.
The program has run into some difficulties. I would appreciate if the forum can help me close and finalise this program which would help me a great deal.
Thanks in advance.

Bob Phillips
07-15-2012, 11:01 AM
Private Sub faReport()
Dim ws As Worksheet
Dim cell As Range
Dim Data As Range

With Worksheets("DATA")

Set Data = .Range(.Range("A2"), .Range("A2").End(xlDown))
For Each cell In Data

Set ws = .Parent.Worksheets.Add(After:=.Parent.Worksheets(.Parent.Worksheets.Count))
ws.Name = cell.Offset(0, 1).Value 'Description
ws.Range("A1").Value = "ABC Private Limited"
ws.Range("A2").Value = "Registered Office Address: Noida (U.P.) 201005, U.P. India"
ws.Range("A3").Value = "Identity of the Fixed Asset"
ws.Range("B3").Value = cell.Offset(0, 1).Value 'ID
ws.Range("A4").Value = "Description of the Asset"
ws.Range("B4").Value = cell.Value 'Description
ws.Range("A5").Value = "Opening Balance Value"
ws.Range("B5").Value = cell.Offset(0, 2).Value 'OpeningBalance
ws.Range("A6").Value = "Depreciation for the year"
ws.Range("B6").Value = cell.Offset(0, 3).Value 'Depreciation
ws.Range("A7").Value = "Closing Balance at the end of the year"
ws.Range("B7").Value = cell.Offset(0, 4).Value 'ClosingBalance
ws.Columns("A:B").AutoFit
Next cell
End With
End Sub

maruduran
07-15-2012, 09:49 PM
Thanx a million for this which helps me a great deal
:clap2: :clap2: