Link to home
Start Free TrialLog in
Avatar of NYQuicksale
NYQuicksaleFlag for United States of America

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

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

Open in new window


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

Open in new window


i've associated cell H1 with worksheet change in prices sheet and the code is
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim sts As String

sts = Range("$H$1").Value

If Target.Address = "$H$1" Then

Call COPYPRICES

End If

End Sub

Open in new window

Avatar of Swapnil Nirmal
Swapnil Nirmal
Flag of India image

where is the subroutine LoopA called in the above codes? i cant see it
ASKER CERTIFIED SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JolyonSpence
JolyonSpence

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).Row)
        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"