CODEWITHSUNDEEP
vbaexceltranspose
Vbasic4now
Vbasic4now

Reputation: 589

Excel moving data from one sheet to another and placing in next empty row

I have values coming from a table that i am trying to write a VBA macro for to move to another sheet where it will be place in the next empty row,

here is what i have so far as far as selecting the data goes:

'storing the values to be moved
Dim DayID As String
Dim Shift As Integer
Dim Operator As String
Dim Operation As String
Dim PartNum As String
Dim Asset As String

'placing selected cells
Sheets("Raw Data").Select

   Range("A10000").Select
   Selection.End(xlUp).Select
   ActiveCell.Offset(1, 0).select

   ActiveCell.Value = DayID

I got to this point to see if what i had worked with just putting the date in ad it did not. I am new to VBA and don't fully understand what i am doing yet so any help is appreciated!

the columns I'm placing the data in are in columns A, M, O, Q, N, and P respectively if that helps

Upvotes: 0

Views: 95

Answers (2)

Paaqua Grant
Paaqua Grant

Reputation: 160

This assumes that you are working on the same workbook that contains the code. If not, you can change "ThisWorkbook" to "ActiveWorkbook". I included the With wsTarget even though it is currently excessive, with the belief that as you build this subroutine up, it will become increasingly relevant. Edited to place the first three variables into their appropriate columns. I leave it to you to fill in the remaining code:

Sub FirstStep()

'storing the values to be moved
Dim DayID As String
Dim Shift As Integer
Dim Operator As String
Dim Operation As String
Dim PartNum As String
Dim Asset As String

Dim wsTarget As Worksheet

Set wsTarget = ThisWorkbook.Worksheets("Raw Data") 'Would be much better to change the CodeName of the sheet and reference directly.

'placing selected cells
With wsTarget
    .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = DayID
    .Cells(Rows.Count, 13).End(xlUp).Offset(1, 0).Value = Shift
    .Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Value = Operator
End With

End Sub

Upvotes: 1

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

Without fully knowing what values you want to fill in, what sheet you are starting with and how exactly you want the results to look, something like the following should at least get you started.

Sub test()

Dim rData As Worksheet
Dim lRow As Long
Dim arr(5) As Variant

Set rData = Sheets("Raw Data")
arr(0) = "A"
arr(1) = "M"
arr(2) = "O"
arr(3) = "Q"
arr(4) = "N"
arr(5) = "P"

With rData

    For Each element In arr

        lRow = .Cells(.Rows.Count, element).End(xlUp).Row + 1
        .Cells(lRow, element).Value = "Value in Column " & element

    Next element

End With

End Sub

Upvotes: 1

Related Questions