Alex Piotto
Board Regular
- Joined
- Jul 5, 2016
- Messages
- 82
- Office Version
- 2007
- Platform
- Windows
Hi! Here we go...
From a ComboBox value in sheet1 i am trying to select a range in sheet2 , copy it and paste it in sheet1 in the right place.
sheet1 is named VENDAS
sheet2 is named DBase CLIENTES
I wrote a script, but i am stuck where i need to select the range below the selected cell in row1 (range named TUTTICLIENTI in DBase CLIENTES) while deselecting the first cell, from row2 down to row274.
What to do? A bit lost here...
From a ComboBox value in sheet1 i am trying to select a range in sheet2 , copy it and paste it in sheet1 in the right place.
sheet1 is named VENDAS
sheet2 is named DBase CLIENTES
I wrote a script, but i am stuck where i need to select the range below the selected cell in row1 (range named TUTTICLIENTI in DBase CLIENTES) while deselecting the first cell, from row2 down to row274.
VBA Code:
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Dim miocliente As String
miocliente = Worksheets("VENDAS").OLEObjects("ComboBox1").Object.Value 'get client name from combobox
Dim foundRng As Range
ThisWorkbook.Sheets("DBase CLIENTES").Activate
Set foundRng = ThisWorkbook.Sheets("DBase CLIENTES").Range("TUTTICLIENTI").Find(miocliente) 'look in DBase CLIENTES, range TUTTICLIENTI for same string
If miocliente = "Seleccionar Cliente" Then
'do nothing
Else
If foundRng.Value = miocliente Then
ThisWorkbook.Sheets("DBase CLIENTES").Range(foundRng.Address).Select 'HERE THE CELL WITH THE same STRING IS SELECTED
End If
MsgBox foundRng.Address 'USEFUL TO VIEW THE ADDRESS
'NOW I NEED TO SELECT AND COPY THE RANGE BELOW THE SELECTED CELL IN ROW1 (FROM ROW2 down TO ROW274), IN THE foundRng.Address COLUMN) - HELP HERE!
'FINALLY I WILL PASTE THE COPIED RANGE INTO VENDAS - NO PROBLEM HERE HOPEFULLY...
'ThisWorkbook.Sheets("VENDAS").Activate
'ThisWorkbook.Sheets("VENDAS").Range("B5:C277").Select
'ThisWorkbook.Sheets("VENDAS").Range("B5:C277").PasteSpecial Paste:=xlPasteValues
'ThisWorkbook.Sheets("VENDAS").Range("B5:C277").Borders.LineStyle = xlContinuous
'ThisWorkbook.Sheets("VENDAS").Range("A1").Select
End If
Application.ScreenUpdating = True
End Sub
What to do? A bit lost here...