PDA

View Full Version : copy data from a range in sheet into rows in another sheet



djemy1975
04-02-2016, 10:09 AM
Hi friends ,

What i want here is to copy data from database "BDD" sheet into invoice output but with some restrictions and conditions,

to clarify:
Take data from "BDD"sheet and copy down from "C2" column to "i" column ignoring "F" and "G" .Then from "M" to "Q" and so on whenever column item to column ht is not blank .


herewith my file


thanks in advance

djemy1975
04-03-2016, 01:55 AM
Hi again,

It seems that no reply for my problem here ,but I will never surrender as I keep trying to solve the problem.What I have got so far is this code

Private Sub CommandButton2_Click()
Sheets("Facture Simple").Range("D18:I18").Value = Sheets("BDD").Range("C2:J2").Value
Sheets("Facture Simple").Range("D19:i19").Value = Sheets("BDD").Range("m2:q2").Value
Sheets("Facture Simple").Range("D20:i20").Value = Sheets("BDD").Range("r2:v2").Value
Sheets("Facture Simple").Range("D21:i21").Value = Sheets("BDD").Range("w2:aa2").Value
End Sub



could someone help me to always copy the last non empty range to the other sheet whenever I click that button.

Thanks in advance,

p45cal
04-04-2016, 02:36 AM
Select any cell on the row of BDD containig the facture you want to create BEFORE clicking the button:
Private Sub CommandButton2_Click()
rw = ActiveCell.Row
Sheets("BDD").Range("C" & rw & ":E" & rw & ",H" & rw & ":I" & rw).Copy
Sheets("Facture Simple").Range("D18").PasteSpecial Paste:=xlPasteValues
destnRw = 19
For colm = 13 To 58 Step 5
If Len(Sheets("BDD").Cells(rw, colm + 4).Value) > 0 Then
Sheets("BDD").Cells(rw, colm).Resize(, 5).Copy
Sheets("Facture Simple").Range("D" & destnRw).PasteSpecial Paste:=xlPasteValues
destnRw = destnRw + 1
End If
Next colm
Application.CutCopyMode = False
End Sub

djemy1975
04-04-2016, 04:05 AM
Select any cell on the row of BDD containig the facture you want to create BEFORE clicking the button:
Private Sub CommandButton2_Click()
rw = ActiveCell.Row
Sheets("BDD").Range("C" & rw & ":E" & rw & ",H" & rw & ":I" & rw).Copy
Sheets("Facture Simple").Range("D18").PasteSpecial Paste:=xlPasteValues
destnRw = 19
For colm = 13 To 58 Step 5
If Len(Sheets("BDD").Cells(rw, colm + 4).Value) > 0 Then
Sheets("BDD").Cells(rw, colm).Resize(, 5).Copy
Sheets("Facture Simple").Range("D" & destnRw).PasteSpecial Paste:=xlPasteValues
destnRw = destnRw + 1
End If
Next colm
Application.CutCopyMode = False
End Sub



Thanks a lot again fro your kindness.But is it possible to subsitutute row selection before click with another procedure for I will not access sheets after that .I would be gratfull if any .I will give it a try however

Thank you

p45cal
04-04-2016, 04:26 AM
But is it possible to subsitutute row selection before click with another procedure for I will not access sheets after that
Either make rw a global variable and delete:
rw = Activecell.row
or
use another global variable and replace
rw = Activecell.row
with something along the lines of:
rw = youGlobalvariableNameHere

djemy1975
04-04-2016, 05:49 AM
I mean that I want to use the button from a userform without accessing the sheet "BDD".Is it possible? something like a combobox

p45cal
04-04-2016, 06:11 AM
I mean that I want to use the button from a userform without accessing the sheet.Is it possible?Of course it is; use the same code but just set the value of rw in a different way - how? I haven't the foggiest since I don't know which userform, which button, how you want to go about it etc. etc.

djemy1975
04-04-2016, 06:41 AM
Hi again

Herewith my form to apply the code .I have put a button named "copy to invoice" in my form to make use of your code .Could you help me now.

Thanks in advance

p45cal
04-04-2016, 09:09 AM
You supply a file with only 1 row on BDD sheet to test/write code to select from one of many - really useful.
When the form comes up how does it load data in? (Yes it loads data from Fiche Clients but I don't see it loading any data from BDD)
There's a button 'Copy to Invoice' (no code behind it); Copy what to invoice?
No instructions as to what to do.

Come on, you'll have to do better than that.

djemy1975
04-05-2016, 01:16 AM
Dear p45cal,

I am so sorry for the file for I am confused on how to deal with such problem ,but I will try now to make the idea clear for you .

In fact I want to make a form with two choices :

-one item invoice and multi item invoice .I have made two forms but it seems too complicated and I want to simplify the idea making only one form:

For this reason, I made a form to save data into BDD then transfer data from that sheet to invoice output.

Herewith a screenshot of my invoice form with necessary amendments as well as my file attached.

-Also you can find my old file with two forms .I want to get rid of this problem and make an output invoice for single item or multi item invoice, by using either the first option(two forms)or the second option (one form).Please help.

Thanks in advance,

15830

djemy1975
04-06-2016, 01:12 AM
Either make rw a global variable and delete:
rw = Activecell.row
or
use another global variable and replace
rw = Activecell.row
with something along the lines of:
rw = youGlobalvariableNameHere

How about using your code with a combobox to select the range by invoice number and a button to copy that range to another sheet.For that reason I made a sample of my earlier file herewith

p45cal
04-06-2016, 08:36 AM
What you're asking for has moved on from a relatively simple "Take data from "BDD"sheet and copy down from "C2" column to "i" column ignoring "F" and "G" .Then from "M" to "Q" and so on whenever column item to column ht is not blank ." to writing a significant portion of code behind your userform(s).
I don't have the time to do this justice (I'll be otherwise engaged over the next week or so), especially as it's clearly for commercial purposes.
Perhaps start a new thread?

djemy1975
04-06-2016, 11:53 AM
Ok ,thank you .I was so insistent on you forgive me for that please.I will start a new thread taking into account your valuable code given in post N°3 and try to apply them on both userforms.

Thank you so much.