1

Need a little help here.

In the "Data" Tab I want to copy values in column "c2:c1000" and paste in column "a1" of another Tab.

This is what i have so far, 

Dim x As Long
Dim lastRow As Long

lastRow = Worksheet("Data").Cells(3, Columns.Count).End(xlUp).Column

For x = 1 To lastRow
    If Worksheets("Sheet2").Cells(2, "A") = "" Then
        Worksheets("Data").Range("c2:c1000").Copy Destination:=Worksheets("Sheet2").Range(1, "A")

        Sheets("Sheet2").Range("A1").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

    Else
        Worksheets("Data").Range("c2:c1000").Copy Destination:=Worksheets("Sheet2").Cells(2, 
        Columns.Count).End(xlToLeft).Offset(, 1)
       'Sheets("Sheet2").Range("A1").Value = Format(Now, "mm/dd/yyyy HH:mm:ss") --> can't figure how to increment this as this will need to be on the subsequent empty column 
End If

Next
End Sub

Your help will be greatly appreciated! Thank you.

Microsoftnoob
  • 13
  • 4

2 Answers2

0

Much like your LastRow* variable for your source sheet, create a LastColumn variable for your destination sheet, which will find the last used column the same way you are finding your last used row.

Like so: 

Dim LastColumn As Long
LastColumn = Sheets("Audit").Cells(1, Columns.Count).End(xlToLeft).Column

Then use the variable like so: 

Destination:= Worksheets("Audit").Cells(1, LastColumn)

It seems that your code contradicts your question too, in your question you explained the data will be written to the Audit sheet in row 1, using the next column each time but your code looks for values in row 2 in your If statement:

If Worksheets("Audit").Cells(2, "A") = "" Then is the same as If Worksheets("Audit").Range("A2") = "" Then.

If you mean to check the first row, change the 2 to 1.

To help improve your codes efficiency:

(Also see the link to 'how to avoid select' in that question):

You can achieve 'copy/paste' without actually using the 'copy' and 'paste' methods by assigning the value of one range to the other, as example, like so: 

Worksheets("Audit").Cells(1, LastColumn).Resize(999, 1) = Worksheets("Data").Range("c2:c1000").Value

Note: Change the Resize Property rows to suit the source range (in this case you are wanting to move values from C2:C1000).

*The LastRow variable is a bit confusing, as it is looking for the last used column in row 3.

If it's meant to find a column, consider renaming it to avoid confusion later on in debugging.

If it's meant to find the last row, try like this:
LastRow = Worksheet("Data").Cells(Rows.Count, 1).End(xlUp).Row

Samuel Everson
  • 2,097
  • 2
  • 9
  • 24
0

Pasting values first into range A1 and down and then next time to cell B1 and so on, leaves no space for the timestamp to A1, B1 etc. So, I assume that you would like to paste the random values to row 2. So cells A1, B1, ... are left for the timestamp.

Inside the With statements we can refer to properties of the wsAudit so we can replace the "Worksheets("Audit")." reference with just "."

The column.count expression just checks the amount of columns in the worksheet. The expression .Cells(2, Columns.Count) just points to last cell in the row 2. The .End(xlToLeft).Column then looks from this column to left and is supposed to find the last not empty cell on this row. It's basically the same idea that in Excel's sheet you would go to cell XDF2 and hit CTRL+Arrow Left from keyboard. But instead of activating the cell we just want to get the columns index number and then add 1 (the new column) and save it into variable. Now the new column is known. The expression Range(.Cells(2, newColAudit), .Cells(1000, newColAudit)).Value is really the same as e.g. Range("B2:B1000"), but with this we can use the row and column index numbers instead. This is useful as the column number varies.

And as Samuel pointed out the copy paste operation can be avoided by setting the areas equal.

Dim wsAudit As Worksheet
Dim newColAudit As Long

Set wsAudit = Worksheets("Audit")

With wsAudit
    newColAudit = .Cells(2, Columns.Count).End(xlToLeft).Column + 1
    Range(.Cells(2, newColAudit), .Cells(1000, newColAudit)).Value = Worksheets("Data").Range("C2:C1000").Value
    .Cells(1, newColAudit).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
End With
Mika O.
  • 121
  • 5