VBA Need a Nested Loop to shift columns [duplicate]

Question

Not very experienced with coding VBA, but I would appreciate any help this matter. The code below runs fine; however logically, I feel like it could be written better. Anyone have suggestions?

Sub CopyValues()

  'Declare variables
   Dim Sourcedataws As Worksheet
   Dim WStotransfer As Worksheet

  'Declare counter variables
   Dim i As Integer
   Dim lastrow As Long

  'Declare sheet variables
   Set Sourcedataws = ThisWorkbook.Sheets("Source Data")
   Set WStotransferws = ThisWorkbook.Sheets("WStotransfer")

    lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

  For i = 2 To lastrow

If Sourcedataws.Range("AA" & i).Value = "Condition" Then

    WStotransferws.Range("C18") = Sourcedataws.Range("A" & i).Value
    ActiveCell.Offset(0, 1).Select
    WStotransferws.Range("D18") = Sourcedataws.Range("A" & i).Value
    ActiveCell.Offset(0, 1).Select
    WStotransferws.Range("E18") = Sourcedataws.Range("A" & i).Value
    ActiveCell.Offset(0, 1).Select
    WStotransferws.Range("F18") = Sourcedataws.Range("A" & i).Value
    ActiveCell.Offset(0, 1).Select
    WStotransferws.Range("G18") = Sourcedataws.Range("A" & i).Value
    ActiveCell.Offset(0, 1).Select
    WStotransferws.Range("H18") = Sourcedataws.Range("A" & i).Value
    ActiveCell.Offset(0, 1).Select
    WStotransferws.Range("I18") = Sourcedataws.Range("A" & i).Value



End If

Next

End Sub

I'm not sure I fully get your question about the script. Could you put up some sample data and sample output for what the script should do? — takanuva15
– takanuva15, Commented Aug 1, 2017 at 18:37
Pull up your sample data, and use the snipping tool on your computer to get a screenshot and paste it into your post. Do the same for the desired output. (Assuming you use Windows) — takanuva15
– takanuva15, Commented Aug 1, 2017 at 19:33
Also, for questions regarding "how can this be simplified/written better" a better site would be codereview.stackexchange.com — interesting-name-here
– interesting-name-here, Commented Aug 1, 2017 at 19:51

Cyril · Accepted Answer · 2017-08-01 20:29:12Z

1

Try:

Sub CopyValues()

'Declare counter variables
Dim i As Integer, j as Integer, lastrow As Long
'Declare variables
Dim Sourcedataws As Worksheet, WStotransfer As Worksheet
'Declare sheet variables
Set Sourcedataws = ThisWorkbook.Sheets("Source Data")
Set WStotransferws = ThisWorkbook.Sheets("WStotransfer")

lastrow = Sourcedataws.Cells(Sourcedataws.Rows.Count, "A").End(xlUp).Row

WStotransferws.Range("C18:I18").ClearContents

For i = 2 To lastrow
    If WStotransferws.Range("I18").Value="" Then
        If Sourcedataws.Range("AA" & i).Value = "Condition" Then
            Sourcedataws.Range("A"&i).Copy 
            j=WStotransferws.Cells(18, WStotransferws.Columns.Count).End(xlToLeft).Column
            WStotransferws.Cells(18,j+1).PasteSpecial xlPasteValues
            End If
        Else
        End If
    Next i

End Sub

edited Aug 1, 2017 at 20:29

answered Aug 1, 2017 at 18:46

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Need a Nested Loop to shift columns [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related