CODEWITHSUNDEEP
vbaexcelcopy-paste
Tyler
Tyler

Reputation: 616

Copy & Paste into next empty row & column respectively

I asked a number of times on this question & all the while, I being given vague answer, which isn't much help. Thus I just research on my own and came up with the following code from my research. Which works but doesn't exactly give me the desired outcome stated in the image attached. Whereby the codes paste the data from its specified cells but paste in column A which isn't the outcome wanted, but rather to paste from column B onward for sheets DX,DY & DZ. Is there also a way I can get column A to update the date by itself base on Date entered in cell S9 which tag along with the data for sheets DX,DY & DZ. Likewise for sheet RAW, that update row 6 with the date entered in S9 of sheet GP Data

Sub Prism2ndStep()
'
' Prism2ndStep Macro
'
    r = 1
Sheets("GP Data").Range("S12:S14").Copy
If Sheets("GP Data").Range("S12") = Sheets("DX").Range("A65536").End(xlUp) _
    Then r = 0
Sheets("DX").Range("A65536").End(xlUp).Offset(r, 0).PasteSpecial _
    Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False

j = 1
Sheets("GP Data").Range("T12:T14").Copy
If Sheets("GP Data").Range("T12") = Sheets("DX").Range("A65536").End(xlUp) _
    Then j = 0
Sheets("DX").Range("A65536").End(xlUp).Offset(j, 0).PasteSpecial _
    Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False

k = 1
Sheets("GP Data").Range("U12:U14").Copy
If Sheets("GP Data").Range("U12") = Sheets("DX").Range("A65536").End(xlUp) _
    Then k = 0
Sheets("DX").Range("A65536").End(xlUp).Offset(k, 0).PasteSpecial _
    Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False


Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("GP Data")
Set pasteSheet = Worksheets("RAW")

copySheet.Range("P12:R14").Copy
With pasteSheet
.Cells(7, .Columns.Count).End(xlToLeft).Offset(0, 7).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

End With

End Sub

Upvotes: 0

Views: 463

Answers (1)

Chinwobble
Chinwobble

Reputation: 652

Try this macro for copying data from "GP Data" S12:S14 and pasting it into column B:D in DX tab. Edited

Sub prism2ndStep()
    'get date from cell p9
    strdate = Sheets("GP Data").Range("S9").Value
    arrData = Sheets("GP Data").Range("S12:S14").Value
    Set rngwrite = Nothing
    Set rngwrite = Sheets("DX").Range("A:A").Find(strdate, LookIn:=xlFormulas)
    Do While rngwrite Is Nothing
        With Sheets("DX").Range("A60000")
            .End(xlUp).AutoFill (.End(xlUp).Resize(2))
        End With
        Set rngwrite = Sheets("DX").Range("A:A").Find(CDate(strdate), LookIn:=xlFormulas)
    Loop
    rngwrite.Offset(, 1).Resize(, 3).Value = Application.Transpose(arrData)

End Sub

Sub prism2ndStep2()
    'get data
    arrData = Sheets("GP Data").Range("P12:R14").Value
    'find get the first non-blank column in row 7 from right to left
    Set rngwrite = Sheets("RAW").Range("IV7").End(xlToLeft).Offset(, 1)
    'paste data
    rngwrite.Resize(3, 3).Value = arrData
    'drag dates across row 7
    rngwrite.Offset(-1).Value = rngwrite.Offset(-1, -3).Value + 1
End Sub

Upvotes: 1

Related Questions