Hai experts,
How to duplicate the data from 1 excel sheet to another excel sheet 2.
Lets say -
Name Voucher Value Voucher Number
-
lee 300.00 58419-58421
-
meena 300.00 58422-58424
-
Tan 300.00 58425-58427
I would like to filter the data accoring to the voucher number and the result is like this in excel -
58419 lee 300.00 58419-58421
-
58420 lee 300.00 58419-58421
-
58421 lee 300.00 58419-58421
-
58422 meena 300.00 58422-58424
-
58423 meena 300.00 58422-58424
-
58424 meena 300.00 58422-58424
-
58425 Tan 300.00 58425-58427
-
58426 Tan 300.00 58425-58427
-
58427 Tan 300.00 58425-58427
I would like to filter the 1st sheet (input data) into second sheet in VBA MACRO.
The coding as below - Sub Duplicate
-
Dim oDoc As Object, oSheet As Object, oCell As Object, oCell2 As Object, oCell3 As Object, oString As String
-
Dim oCells As Object
-
Dim oCursors As Object
-
Dim aAddresss As Variant
-
-
REM Define what sheet to used
-
oDoc =ThisComponent
-
oSheet =oDoc.CurrentController.ActiveSheet
-
oSheet2=oDoc.Sheets.getByIndex(1) '2nd Sheet
-
-
REM Get the value of the LastUsedRow & LastUsedColumn
-
oCells = oSheet.GetCellbyPosition(0, 0)
-
oCursors = oSheet.createCursorByRange(oCells)
-
oCursors.GotoEndOfUsedArea(True)
-
aAddress = oCursors.RangeAddress
-
LastUsedRow = aAddress.EndRow
-
LastUsedColumn = aAddress.EndColumn
-
-
'Row2Print=row printed row
-
'l=last used row in Sheet2
-
-
-
For i=0 to LastUsedRow
-
'oCell2=ThisComponent.CurrentSelection.getCellAddress 'Currently Selected Cell
-
oSelect=ThisComponent.CurrentSelection.getRangeAddress
-
oString = oSheet.GetCellbyPosition(oSelect.StartColumn, i).getString() 'IMPORTANT (Need Revision)
-
'getCellByPosition(Column,Row)
-
oRight = Val(Right(oString,Len(oString)-InStr(1, oString, "-")))
-
oLeft = Val(Left(oString,Len(oString)-InStr(1, oString, "-")))
-
Row2Print = oRight - oLeft
-
oRangeOrg = oSheet.getCellRangeByName("A"&(i+1)&":O"&(i+1)).RangeAddress ' copy range
-
-
REM Begin Pasting the Value
-
For j=0 to Row2Print
-
k=k+1
-
oRangeCpy = oSheet2.getCellRangeByName("B"&k).RangeAddress ' insert range
-
oCellCpy = oSheet2.getCellByPosition(oRangeCpy.StartColumn,oRangeCpy.StartRow).CellAddress ' insert position
-
oSheet.CopyRange(oCellCpy, oRangeOrg) ' copy
-
Next
-
-
'oSheet2=oDoc.Sheets.getByIndex(1) '2nd Sheet
-
'oCells = oSheet2.GetCellbyPosition(0, 0)
-
'oCursors = oSheet2.createCursorByRange(oCells)
-
'oCursors.GotoEndOfUsedArea(True)
-
'aAddress = oCursors.RangeAddress
-
'LastUsedRow = aAddress.EndRow
-
'LastUsedColumn = aAddress.EndColumn
-
-
For l=0 to Row2Print
-
oCell4=oSheet2.getCellByPosition(0,m) 'A1
-
oCell4.setString(oLeft)
-
oLeft=oLeft+1
-
m=m+1
-
Next
-
Next i
-
'oCell.NumberFormat=2 '23658.00
-
'oCell.SetValue(12345)
-
'oCell.SetString("oops")
-
'oCell.setFormula("=FUNCTION()")
-
'oCell.IsCellBackgroundTransparent = TRUE
-
'oCell.CellBackColor = RGB(255,141,56)
-
End Sub
-
-
Function GetLastUsedRow(oSheets as Object) as Integer
-
Dim oCells As Object
-
Dim oCursors As Object
-
Dim aAddresss As Variant
-
-
oCells = oSheets.GetCellbyPosition(0, 0)
-
oCursors = oSheets.createCursorByRange(oCells)
-
oCursors.GotoEndOfUsedArea(True)
-
aAddresss = oCursors.RangeAddress
-
GetLastUsedRow = aAddresss.EndRow
-
End Function
-
-
Function GetLastUsedColumn(oSheet as Object) as Integer
-
Dim oCell As Object
-
Dim oCursor As Object
-
Dim aAddress As Variant
-
oCell = oSheet.GetCellbyPosition( 0, 0 )
-
oCursor = oSheet.createCursorByRange(oCell)
-
oCursor.GotoEndOfUsedArea(True)
-
aAddress = oCursor.RangeAddress
-
GetLastUsedColumn = aAddress.EndColumn
-
End Function
-
-
Sub SelRow()
-
Dim oSheet
-
Dim oRow
-
oSheet = ThisComponent.getSheets().getByIndex(0)
-
oRow = oSheet.getRows().getByIndex(2)
-
ThisComponent.getCurrentController().select(oRow)
-
End Sub
-
-
Sub CopySpreadsheetRange
-
oSheet1 = ThisComponent.Sheets.getByIndex(0) ' sheet no 1, original
-
oSheet2 = ThisComponent.Sheets.getByIndex(1) ' sheet no 2
-
-
oRangeOrg = oSheet1.getCellRangeByName("A1:C10").RangeAddress ' copy range
-
oRangeCpy = oSheet2.getCellRangeByName("A1:C10").RangeAddress ' insert range
-
-
oCellCpy = oSheet2.getCellByPosition(oRangeCpy.StartColumn,_
-
oRangeCpy.StartRow).CellAddress ' insert position
-
-
oSheet1.CopyRange(oCellCpy, oRangeOrg) ' copy
-
End Sub
-
'----------------------------------------------------------------------------------------
-
-
Function IsSpreadsheetDoc(oDoc) As Boolean
-
Dim s$ : s$ = "com.sun.star.sheet.SpreadsheetDocument"
-
IsSpreadsheetDoc = oDoc.SupportsService(s$)
-
End Function
-
-
Sub checking( )
-
MsgBox IsSpreadsheetDoc(thisComponent)
-
End Sub
-
-
Sub ExampleGetValue
-
Dim oDoc As Object, oSheet As Object, oCell As Object
-
oDoc=ThisComponent
-
oSheet=oDoc.Sheets.getByName("Sheet1")
-
oCell=oSheet.getCellByposition(0,0) 'A1
-
Rem a cell's contents can have one of the three following types:
-
Print oCell.getValue()
-
'Print oCell.getString()
-
'Print oCell.getFormula()
-
End Sub
-
-
Sub SelectedCells
-
oSelect=ThisComponent.CurrentSelection.getRangeAddress
-
oSelectColumn=ThisComponent.CurrentSelection.Columns
-
oSelectRow=ThisComponent.CurrentSelection.Rows
-
-
CountColumn=oSelectColumn.getCount
-
CountRow=oSelectRow.getCount
-
-
oSelectSC=oSelectColumn.getByIndex(0).getName
-
oSelectEC=oSelectColumn.getByIndex(CountColumn-1).getName
-
-
oSelectSR=oSelect.StartRow+1
-
oSelectER=oSelect.EndRow+1
-
NoCell=(CountColumn*CountRow)
-
-
If CountColumn=1 AND CountRow=1 Then
-
MsgBox("Cell " + oSelectSC + oSelectSR + chr(13) + "Cell No = " + NoCell,, "SelectedCells")
-
Else
-
MsgBox("Range(" + oSelectSC + oSelectSR + ":" + oSelectEC + oSelectER + ")" + chr(13) + "Cell No = " + NoCell,, "SelectedCells")
-
End If
-
End Sub
-
-
Sub Analize
-
sSum="=SUM("+GetAddress+")"
-
sAverage="=AVERAGE("+GetAddress+")"
-
sMin="=MIN("+GetAddress+")"
-
sMax="=MAX("+GetAddress+")"
-
CellPos(7,6).setString(GetAddress)
-
CellPos(7,8).setFormula(sSum)
-
CellPos(7,8).NumberFormat=2
-
CellPos(7,10).setFormula(sAverage)
-
CellPos(7,10).NumberFormat=2
-
CellPos(7,12).setFormula(sMin)
-
CellPos(7,12).NumberFormat=2
-
CellPos(7,14).setFormula(sMax)
-
CellPos(7,14).NumberFormat=2
-
End sub
-
-
Function GetAddress 'selected cell(s)
-
oSelect=ThisComponent.CurrentSelection.getRangeAddress
-
oSelectColumn=ThisComponent.CurrentSelection.Columns
-
oSelectRow=ThisComponent.CurrentSelection.Rows
-
-
CountColumn=oSelectColumn.getCount
-
CountRow=oSelectRow.getCount
-
-
oSelectSC=oSelectColumn.getByIndex(0).getName
-
oSelectEC=oSelectColumn.getByIndex(CountColumn-1).getName
-
-
oSelectSR=oSelect.StartRow+1
-
oSelectER=oSelect.EndRow+1
-
NoCell=(CountColumn*CountRow)
-
-
If CountColumn=1 AND CountRow=1 then
-
GetAddress=oSelectSC+oSelectSR
-
Else
-
GetAddress=oSelectSC+oSelectSR+":"+oSelectEC+oSelectER
-
End If
-
End Function
-
Function CellPos(lColumn As Long,lRow As Long)
-
CellPos= ActiveSheet.getCellByPosition (lColumn,lRow)
-
End Function
-
Function ActiveSheet
-
ActiveSheet=StarDesktop.CurrentComponent.CurrentController.ActiveSheet
-
End Function
-
Sub DeleteDbRange(sRangeName As String)
-
oRange=ThisComponent.DatabaseRanges
-
oRange.removeByName (sRangeName)
-
End Sub
KINDLY HELP ME SINCE THE CODING NOT WORKING INTEMETINLY..NE ED HELP!!
1 2688 jimatqsi 1,277
Recognized Expert Top Contributor
Wrong forum, I think you want the Excel forum.
Jim
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Hi5 |
last post by:
Hi,
I am new to access I usedto work in Oracle and Mysql.
I am after a way that enables me to populate a database I designed in
access with lots of data which can be sorted in excel sheets,
The source of all data is a very large size (100*2000) excel spread
sheet In which some data types are absulotely Irelevant to access data
types in my DB there fore I changed It to a number of excel sheets
|
by: pmud |
last post by:
Hi,
I need to use an Excel Sheet in ASP.NET application so that the users can
enter (copy, paste ) large number of rows in this Excel Sheet. Also, Whatever
the USER ENETRS needs to go to the SQL DATABASE, probably by the click of a
button.
Is this possible? & what is the BEST APPROACH for doing this? & also if any
links are there do tell those to me too coz I have no idea how to go about
doing it.
|
by: Mamatha |
last post by:
Hi
I want to export data from textfile to Excel sheet in
VB.NET application.When i copy the text file into Excel
sheet the data of text file are copied to only one cell of
the Sheet.
Now i want like this: Each item of the textfile will be
displayed into different cell of the sheet(one item
corresponds to one cell horizontally).How can write code
for this in VB.NET.
|
by: dba123 |
last post by:
I need some help and direction on what classes and an example or two
(article) on how to read an Excel Worksheet and insert one column into a
database table column. I am using .NET 2.0 only. What namespaces and
classes should I use and how?
--
dba123
|
by: KK |
last post by:
Hi,
I am using Response object in vb.net to download an excel sheet with
data.
It worked well and I could download excel file with data. But suddenly
It started downloading only blank excel sheet. even though the data
exists. Do I need to do any refreshing? or do I need to re-start IIS?
Dim dt As DataTable = DSCAG.Tables(0)
| |
by: kannabiran |
last post by:
Hi everyone,
Im using the C# asp.net as the front end and i need to use the Excel sheet as the back end.i want to select the records which is available in the excel sheet using query and insert,update,delete every thing i want to achieve it from the front end..suppose im having the two fields in the excel sheet like ID and Name,im having two textboxes in the front end to view the record.
1.what is the provider to connect the excel...
|
by: uprakash14 |
last post by:
i have excel sheet and i want to open the that data in datagrid or msfexgrid..
plz help me .. or give me code
|
by: pvenu |
last post by:
Hi,
I know basic perl (regular expressions, pattern matching, string manipulation, reading writing into text files).
Yet, my requirement is to read an input text file -> process this input file through a Perl script and the output generated from this script is to be written into an Excel sheet file (into each cells of the sheet).
Can you please provide me pointers on this. Example code for reading and writing into Excel (spread) sheets...
|
by: inepu |
last post by:
I have some excel sheets that I would like to manage in access. I've created the access tables and relationships; the excel sheet is only one table and with duplicate data, I'd like to make sure that when I import the sheet, the proper tables are filled correctly.
For example, the excel sheet is in the format
clientID Name phone City Country
In acces, I have a "Cities" table and a "Coutries" table, with the fields (e.g. for cities)
ID...
|
by: TG |
last post by:
hi!
I am trying to create a sql server table from an excel sheet.
Here is the code I have:
'This procedure the xlsx file and dumps it to a table in SQL Server
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |