[Solved] Copy visible cells and paste values
[Solved] Copy visible cells and paste values
greetings
I'm searching for a macro to copy the cells that have been filtered into Auto Filter (Visible cells), and Paste Values into another worksheet
Problem description :
in worksheet1 has Auto Filter from column A to column Z
you need to copy the Visible filtered cells from column A to column E and paste only the values in the first empty cell of column A in worksheet2
return to worksheet1 and copy the Visible filtered cells from column I to column L and paste only the values in the first empty cell of column F in worksheet2
go back to worksheet1 and copy the Visible filtered cells from column U to column W and paste only the values in the first empty cell of column J in worksheet2
then go back to the worksheet1 and undo all filters, ie Clear All Filters
hugs
I'm searching for a macro to copy the cells that have been filtered into Auto Filter (Visible cells), and Paste Values into another worksheet
Problem description :
in worksheet1 has Auto Filter from column A to column Z
you need to copy the Visible filtered cells from column A to column E and paste only the values in the first empty cell of column A in worksheet2
return to worksheet1 and copy the Visible filtered cells from column I to column L and paste only the values in the first empty cell of column F in worksheet2
go back to worksheet1 and copy the Visible filtered cells from column U to column W and paste only the values in the first empty cell of column J in worksheet2
then go back to the worksheet1 and undo all filters, ie Clear All Filters
hugs
Last edited by Math on Fri Nov 23, 2018 6:25 pm, edited 2 times in total.
LibreOffice 5.4.4.2 on Windows 7
Re: Copy visible cells and paste values into another workshe
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy visible cells and paste values into another workshe
thanks for the support link suggested, unfortunately I could not adapt to my problem
I found other support links:
http://document-foundation-mail-archive ... 64486.html
viewtopic.php?f=20&t=58810
unfortunately I also could not adapt to my problem
hugs
I found other support links:
http://document-foundation-mail-archive ... 64486.html
viewtopic.php?f=20&t=58810
unfortunately I also could not adapt to my problem
hugs
LibreOffice 5.4.4.2 on Windows 7
Re: Copy visible cells and paste values into another workshe
learn how to program
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy visible cells and paste values into another workshe
Friends, if you can help me in building this code, I am grateful now
hugs
hugs
LibreOffice 5.4.4.2 on Windows 7
Re: Copy visible cells and paste values into another workshe
Please upload your StarBasic code here (together with the API callings, and an ODF type sample file - but not some copied old VBA code), and we will try to help you to fix the bugs in your code.Friends, if you can help me in building this code, I am grateful now
If there is not any code yet, we can not help you. You must start your project.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Copy visible cells and paste values into another workshe
He has all the StarBasic code that is needed.
Code: Select all
Function copyTiledRanges(oDoc,oRanges,oTopLeft, bVal As Boolean)
REM Takes a spreadsheet doc, a SheetCellRanges collection and a SheetCell object
REM when bVal = True, all formulas will be replaced by values.
REM The ranges should be tiled, e.g. adjacent ranges separated by hidden rows and columns
REM Returns the merged target range
[...]
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy visible cells and paste values into another workshe
I found a VBA code that does in Excel what I need
If you can help me turn to LibreOffice (BASIC), I am already grateful
source: https://forum.baboo.com.br/index.php?/t ... filtradas/
VBA code
Sub CopyFilter()
Dim rng As Range
Dim rng2 As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Plan2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Plan2").Range("A1")
End If
ActiveSheet.ShowAllData
End Sub
If you can help me turn to LibreOffice (BASIC), I am already grateful
source: https://forum.baboo.com.br/index.php?/t ... filtradas/
VBA code
Sub CopyFilter()
Dim rng As Range
Dim rng2 As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Plan2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Plan2").Range("A1")
End If
ActiveSheet.ShowAllData
End Sub
LibreOffice 5.4.4.2 on Windows 7
Re: Copy visible cells and paste values into another workshe
It is not possible to achieve with some pure StarBasic commands and statements. You must study and use the API functions of the AOO/LO.If you can help me turn to LibreOffice (BASIC), I am already grateful
API : Application Programming Interface.
Please download and read Andrew Pitonyak's free macro books.
(This forum is NOT a free macro translation service!)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Copy visible cells and paste values into another workshe
This is about real programming. You do not even know VBA, so you are completely lost when confronted with a task that can not be solved by means of copy/paste or macro recording. You are by far not the only "VBA expert" of the same calibre and we are no human macro recorders. I've got work to do.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy visible cells and paste values into another workshe
[Solved]
Thank you for the contributions of all the friends who helped
hugs
Thank you for the contributions of all the friends who helped
hugs
LibreOffice 5.4.4.2 on Windows 7
Re: [Solved] Copy visible cells and paste values
You don't share your solution with the public?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice