NYQuicksale
asked on
Loop..........Copy Range and offset
i've a code that copy data from one sheet and paste it into another,but when attached with the loop, the copyprices code don't work,just loop runs and ends without copying,just the range is offset
my loop code is
i've associated cell H1 with worksheet change in prices sheet and the code is
Private Sub Worksheet_SelectionChange( ByVal Target As Range)
Sub COPYPRICES()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("PRICES")
Set ws2 = Sheets("SHEET1")
ws1.Range("T1:AT225").Copy
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub
my loop code is
Sub loopA()
Dim cel As Range
For Each cel In Range("A1:A" & Range("A1").End(xlDown).Row)
[h1] = cel.Value
Next cel
End Sub
i've associated cell H1 with worksheet change in prices sheet and the code is
Private Sub Worksheet_SelectionChange(
Dim sts As String
sts = Range("$H$1").Value
If Target.Address = "$H$1" Then
Call COPYPRICES
End If
End Sub
where is the subroutine LoopA called in the above codes? i cant see it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not sure what you want but, assuming you want to loop through each populate cell in column A, each time changing the value of cell H1 with the value of the cell in column A, which in turn executes the CopyPrices sub, then put the code to call the Sub CopyPrices in WorkSheet_Change:
in ThisWorkBook VBA code:
Sub COPYPRICES()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("PRICES")
Set ws2 = Sheets("SHEET1")
ws1.Range("T1:AT225").Copy
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub
In PRICES WorkSheet VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sts As String
sts = Range("$H$1").Value
If Target.Address = "$H$1" Then
ThisWorkbook.COPYPRICES
End If
End Sub
Sub loopA()
Dim cel As Range
For Each cel In Range("A1:A" & Range("A1").End(xlDown).Ro w)
Cells(1, 8) = cel.Value
Next cel
End Sub
in ThisWorkBook VBA code:
Sub COPYPRICES()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("PRICES")
Set ws2 = Sheets("SHEET1")
ws1.Range("T1:AT225").Copy
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub
In PRICES WorkSheet VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sts As String
sts = Range("$H$1").Value
If Target.Address = "$H$1" Then
ThisWorkbook.COPYPRICES
End If
End Sub
Sub loopA()
Dim cel As Range
For Each cel In Range("A1:A" & Range("A1").End(xlDown).Ro
Cells(1, 8) = cel.Value
Next cel
End Sub
sorry i overlooked it.
You should use Worksheet_Change instead of Worksheet_SelectionChange as you are changing the value and not he selection.
your selection will remain the same and value will change in the loop. Hence use "Worksheet_Change"
You should use Worksheet_Change instead of Worksheet_SelectionChange as you are changing the value and not he selection.
your selection will remain the same and value will change in the loop. Hence use "Worksheet_Change"