|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to fetch data from one sheet to another with complete details and date wise as well
I am trying to post entries in 1st sheet but I am looking to fetch complete data on account title A is fetched in other sheet kindly help me file is attached |
#2
|
|||
|
|||
Hi Harisjawed86,
Nobody has responded to this so I will take a shot at it. I have pasted a code below that should do the trick. A couple of things before you run it though. Your worksheets A and B have a lot of scattered formatting. The code is not causing this. Your first worksheet has a blank after the table header. This makes it difficult to determine when a loop should start and end. Right now it is hard coded as the amount of rows you have but this is not the standard way I would code it. Please insert this code into a new module of your workbook. Save a backup, run it and then look at the results. Code:
Option Explicit Sub FetchData() 'Looks at Column F and populates the result to a worksheet 'based off the value A or B Dim CheckRow As Long, TotalRows As Long, ResultRowA As Long, ResultRowB As Long Dim wb As Workbook, ws As Worksheet, wsA As Worksheet, wsB As Worksheet Dim DateVal() As String, Des() As String, Debit() As String, Credit() As String Dim TR As Long, x As Integer, CheckValue As String 'set up and assign the variables/objects Set wb = ThisWorkbook Set ws = wb.Worksheets("Day Book") Set wsA = wb.Worksheets("A") Set wsB = wb.Worksheets("B") x = 0 'to be used for arrays ResultRowA = 4 ResultRowB = 4 TotalRows = 454 'Change this to your last row otherwise we can write a loop to find the last row. TR = TotalRows ' Easier for redimming arrays ReDim DateVal(TR) As String, Des(TR) As String, Debit(TR) As String, Credit(TR) As String For CheckRow = 7 To TotalRows CheckValue = ws.Range("F" & CheckRow).Value DateVal(x) = ws.Range("A" & CheckRow).Value Des(x) = ws.Range("B" & CheckRow).Value Debit(x) = ws.Range("C" & CheckRow).Value Credit(x) = ws.Range("D" & CheckRow).Value If UCase(CheckValue) = "A" Then wsA.Range("A" & ResultRowA & ":D" & ResultRowA) = Array(DateVal(x), Des(x), Debit(x), Credit(x)) ResultRowA = ResultRowA + 1 x = x + 1 End If If UCase(CheckValue) = "B" Then wsB.Range("A" & ResultRowB & ":D" & ResultRowB) = Array(DateVal(x), Des(x), Debit(x), Credit(x)) ResultRowB = ResultRowB + 1 x = x + 1 End If Next CheckRow MsgBox "done" End Sub Thanks |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help Coloring a cell in Sheet one if data is missing from another sheet | Aeducan | Excel | 1 | 06-22-2014 04:49 PM |
Assigning finish date to work with %complete | Guloluseus | Project | 3 | 12-12-2013 10:16 AM |
sheet 2 data highlight in sheet 1 | gsrikanth | Excel | 1 | 04-21-2012 06:25 PM |
VISIO - Formatting Shape Data Details TABLE | Lora | Visio | 0 | 07-19-2010 12:15 PM |
Percent Complete doesn't change End Date?? | jrwpmw | Project | 2 | 03-11-2010 01:40 PM |