Working with the Columns function - Excel VBA

Question

I have been looking around the site for a while for an answer to this question but no luck just yet. I have this code where I loop through a row of numbers and depending on what number is in the cell at the time, determines what I copy and paste to the sheet. I am using Columns for this because it is the only way I can make my code dynamic. It works but when I paste I would like to paste in cells lower than where it's pasting right now. I was wondering if Columns had a way of specifying what column and where to paste my data.

Code:

Dim sh      As Worksheet
Dim rw      As Range
Dim row     As Range
Dim cell    As Range
Dim RowCount As Integer

Set rw = Range("A5:CG5")
Set sh = ActiveSheet

For Each row In rw.Rows
    For Each cell In row.Cells
        Select Case cell.Value
        Case "2"
            ThisWorkbook.Worksheets("Sheet1").Range("E27:E51").Copy Destination:=Sheets("Sheet2").Columns(4)
        End Select
    Next cell
Next row

You could use .Cells (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-cells-property-excel). Example: `.Cells(row,column) = "Something or/"& Variable (Could be another .Cells)` And use your For loop with an `i=i+1` or a loop `For i=first_column To last_column` — danieltakeshi, Aug 11 '17 at 16:01
@danieltakeshi could I do something like this? ThisWorkbook.Worksheets("Sheet1").Range("E27:E51").Copy Range(Cells(i,5),Cells(i,5) — unclass123, Aug 11 '17 at 16:06
You want to copy this range to what cells and sheet? And you can't make this way. Here is an example: https://stackoverflow.com/questions/42420280/copy-and-paste-dynamic-ranges-to-new-sheet-in-excel-with-vba and one a bit more advanced https://stackoverflow.com/questions/12138624/vba-copy-paste-with-dynamic-range — danieltakeshi, Aug 11 '17 at 16:07
@danieltakeshi yeah for example on my sheet1 the cells im copying start at row 27 and when I paste it to my new sheet it is at cell D1. I would like it to paste to D27 but do so dynamically so I can do some more changes to it later — unclass123, Aug 11 '17 at 16:14
@danieltakeshi no, it's just the last row in my range of cells that I want to copy — unclass123, Aug 11 '17 at 16:18
As Jeeped said, use `Destination:=Sheets("Sheet2").Cells(27, 5)` or `Destination:=Worksheets(2).Range("E27")` I was overhinking, as to make it easier in making dynamic workbooks in the future. Copy might be avoided if the workbook is too big and have many worksheets, because first you have to select the Sheet you will copy from `Worksheets("Sheet1").Select` — danieltakeshi, Aug 11 '17 at 16:38
@danieltakeshi one last question actually. If I wanted to make the column dynamic by using ActiveCell.Column, would it work in a for loop? so far it isn't incrementing correctly. — unclass123, Aug 11 '17 at 17:38
Refer to this so you avoid `.Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook` https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba And ask another question so we don't turn the Comments into a chat — danieltakeshi, Aug 11 '17 at 17:50

danieltakeshi · Accepted Answer · 2017-08-11T18:36:30.627

Your problem can be solved as Jeeped said, use Destination:=Sheets("Sheet2").Cells(27, 5) or Destination:=Worksheets(2).Range("E27")

Since you want to learn a little bit more, i made an example explanation:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-column-property-excel On the link it is explained that .Column:

Column A returns 1, column B returns 2, and so on.

And the same is with the .Rows

Use .Cells https://msdn.microsoft.com/pt-br/library/office/ff194567.aspx So you can use the .Cells(Rows,Columns) or .Cells(Index from a Range) or the entire Object:

With Worksheets("Sheet1").Cells.Font 
.Name = "Arial" 
.Size = 8 
End With

So an example if you want to turn your spreadsheet dynamical: to copy from range $E$27 to last row with something written from column $E on Sheet1 To the last column with nothing written on row 1 on Sheet2.

    Sub test()
'Declare variables here
Dim sht1, sht2 As Worksheet

'sht1 has the data and sht2 is the output Worksheet, you can change the names

last_row = Worksheets(1).Range("E65536").End(xlUp).Row
last_column = Worksheets(2).Cells(1, sht1.Columns.Count).End(xlToLeft).Column

'Data add
For i = 27 To last_row
'Start from Row 27
    Worksheets(2).Cells(i - 26, last_column + 1) = Worksheets(1).Cells(i, 5)
Next i

MsgBox "Data Updated"
End Sub

And an example of a basic dynamical workbook with i=i+1 and For loops split a single row of data into multiple unique rows into a new sheet to include headers as values and cell contents as values

Working with the Columns function - Excel VBA

1 Answers1