pramod5542
New Member
- Joined
- Feb 12, 2016
- Messages
- 4
I have 2 work sheet
1. sales and 2. invoice
i create a user form to data copy sales to in invoice its working as per column basis but i want to cells range basis.
in user form i used filter so how to its possible?
Its my sales table
I want to this type of in my Invoice Sheet
1. sales and 2. invoice
i create a user form to data copy sales to in invoice its working as per column basis but i want to cells range basis.
in user form i used filter so how to its possible?
Its my sales table
NAME | PASSPORT NO | COUNTRY NAME | DOCUMENT TYPE |
AAAA | 123456 | USA | VISA |
BBBB | 567890 | UK | PASSPORT |
I want to this type of in my Invoice Sheet
DETAILS | ||
NAME : AAAA | ||
PASSPORT NO : 123456 | ||
COUNTRY NAME : USA DOCUMENT TYPE : VISA | ||
NAME : BBBB | ||
PASSPORT NO : 567890 | ||
COUNTRY NAME : UK DOCUMENT TYPE : PASSPORT | ||
VBA Code:
Sub Fill_Invoice_Details()
Dim sh As Worksheet
Dim dsh As Worksheet
Set sh = ThisWorkbook.Sheets("Invoice")
Set dsh = ThisWorkbook.Sheets("Sales")
sh.Range("B16:I33").ClearContents
sh.Range("J5").ClearContents
sh.Range("J10").ClearContents
sh.Range("B6:B11").ClearContents
sh.Range("J37").ClearContents
Dim lr As Long
lr = Application.WorksheetFunction.CountA(dsh.Range("A:A"))
dsh.UsedRange.AutoFilter 1, Me.TextBox1.Value
dsh.Range("L2:L" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
sh.Range("B16").PasteSpecial xlPasteValues
sh.Range("B16").WrapText = True
dsh.Range("M2:M" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
sh.Range("C16").PasteSpecial xlPasteValues
dsh.Range("J2:J" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
sh.Range("D16").PasteSpecial xlPasteValues
dsh.Range("K2:K" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
sh.Range("E16").PasteSpecial xlPasteValues
'sh.Range("F15").PasteSpecial xlPasteValues
'dsh.Range("P2:P" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
'sh.Range("G15").PasteSpecial xlPasteValues
'dsh.Range("Q2:Q" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
'sh.Range("H15").PasteSpecial xlPasteValues
dsh.Range("N2:Q" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
sh.Range("F16").PasteSpecial xlPasteValues
dsh.AutoFilterMode = False
'dsh.Range("J2:Q" & lr + 1).SpecialCells(xlCellTypeVisible).Copy
'sh.Range("E15").PasteSpecial xlPasteValues
dsh.AutoFilterMode = False
sh.Range("J5").Value = Me.TextBox1.Value
sh.Range("J7").Value = Me.TextBox2.Value
sh.Range("J10").Value = Me.TextBox10.Value
sh.Range("B6").Value = Me.ComboBox4.Value
sh.Range("B7").Value = Me.TextBox11.Value
sh.Range("B8").Value = Me.TextBox12.Value
sh.Range("B9").Value = Me.TextBox13.Value
sh.Range("B10").Value = Me.TextBox14.Value
sh.Range("B11").Value = Me.TextBox15.Value
sh.Range("J37").Value = Me.TextBox9.Value
End Sub