EXCEL: How to combine values from two different column into one new column on different sheet

[mir994stan]

Hello to everyone, its my first question here, bcs i couldn't find solution anywhere yet...
I am stuck with my procject again... I tried with formulas but i can t make it work or i can t make it right, and i couldn t find similar topic any where, here is the problem.

As u can see my example in screenshot in this link Radni-Nalog
And here is the link for example workbook

Example_Preview.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

I have 2 worksheets, Sheet1 with some value generator, and Sheet"RadniNalog" where i copy&paste manualy certan values from Sheet1. My goal is to make it work automatically, when i paste data from another Workbook, as shown in screenshot example, i polulate range "A10:C27", range width is constant, always 3 column, but rows can change so number is X. Now i need values from "A10:A27" to copy to next empty column from left to right in Sheet"RadniNalog" from cells in 2nd row. Next i also need to copy Value from cell =F$13$ into the first row in sheet "RadniNalog" (on screenshot example its cell "E1" and that value from F13 needs to act like a Header for values belove it. If Value from header is the same as value in cell "F13" i need to continue adding values under existing ones, and if not move to the next available column. In screenshot example, if cell "D1" from sheet "RandiNalog" is same as cell "F13" in Sheet1, then values from range "A10:A27" should be added under last value in ColumnD. I need some VBA code if possible to make it work as wanted. Thanks in advance

 

[Crystalyzer]

Try this on a copy of your workbook

Sub CopyData()
    Dim t As String
    Dim dRng As Range
    Dim lstCol, lstRow, tCol, dRow As Long
    Dim sWksht, tWksht As Worksheet
    
    Set sWksht = ThisWorkbook.Sheets("Sheet1 ")
    Set tWksht = ThisWorkbook.Sheets("Radninalog")
    
    With sWksht
        t = .Cells(13, 6).Value
        lstRow = .Range("A9").End(xlDown).Row
        Set dRng = .Range("A10:A" & lstRow)
   End With
    
   With tWksht
        lstCol = .Range("A1").End(xlToRight).Column
        
        On Error Resume Next
        tCol = Application.Match(sWksht.Cells(13, 6).Value, .Range(.Cells(1, 1), .Cells(1, lstCol)), 0)
        On Error GoTo 0
        
        If IsError(tCol) Then tCol = 0
        
        If tCol = 0 Then
            .Cells(1, lstCol + 1).Value = t
            dRng.Copy
            .Cells(2, lstCol + 1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        Else
            dRow = .Cells(1, tCol).End(xlDown).Row + 1
            dRng.Copy
            .Cells(dRow, tCol).PasteSpecial xlPasteValues
        End If
    End With
    
End Sub

 

[Marc L]

Crystalyser,​

​

as you use a Variant variable - yes tCol is Variant as you can check in the VBE Locals window ! - with Application.Match​

and checking the result with IsError VBA function so the On Error codelines are useless, you can remove them …​

 

[mir994stan]

Works perfect, thank you sir.

 

[Crystalyzer]

You're welcome @mir994stan

@Marc L when I declare tCol as variant and remove the code for On Error and checking for errors I get the Error 2042 for tCol (which I found is "#N/A") and the code halts.

Sub CopyData()
    Dim t As String
    Dim dRng As Range
    Dim lstCol, lstRow, dRow As Long
    Dim tCol As Variant
    Dim sWksht, tWksht As Worksheet
   
    Set sWksht = ThisWorkbook.Sheets("Sheet1 ")
    Set tWksht = ThisWorkbook.Sheets("Radninalog")
   
    With sWksht
        t = .Cells(13, 6).Value
        lstRow = .Range("A9").End(xlDown).Row
        Set dRng = .Range("A10:A" & lstRow)
   End With
   
   With tWksht
        lstCol = .Range("A1").End(xlToRight).Column
       
        'On Error Resume Next
        tCol = Application.Match(sWksht.Cells(13, 6).Value, .Range(.Cells(1, 1), .Cells(1, lstCol)), 0)
        'On Error GoTo 0
       
        'If IsError(tCol) Then tCol = 0
       
        If tCol = 0 Then
            .Cells(1, lstCol + 1).Value = t
            dRng.Copy
            .Cells(2, lstCol + 1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        Else
            dRow = .Cells(1, tCol).End(xlDown).Row + 1
            dRng.Copy
            .Cells(dRow, tCol).PasteSpecial xlPasteValues
        End If
    End With
   
End Sub

 

[Fluff]

You need to leave this line If IsError(tCol) Then tCol = 0 but can get rid of the other two lines that were commented out.

 

[Marc L]

As a reminder Dim tCol is the same as Dim tCol As Variant …​

​

Like on this codeline Dim lstCol, lstRow, dRow As Long only dRow is Long, previous variables are Variant !​

​

   With tWksht
        lstCol = .Range("A1").End(xlToRight).Column
        tCol = Application.Match(sWksht.Cells(13, 6).Value, .Range(.Cells(1, 1), .Cells(1, lstCol)), 0)

        If IsError(tCol) Then
           .Cells(1, lstCol + 1).Value = t
            dRng.Copy
           .Cells(2, lstCol + 1).PasteSpecial xlPasteValues
        Else
            dRow = .Cells(1, tCol).End(xlDown).Row + 1
            dRng.Copy
           .Cells(dRow, tCol).PasteSpecial xlPasteValues
        End If
            Application.CutCopyMode = False
    End With

 

[Crystalyzer]

so it should be like this if I want them all to be Long, got it. I thought the declaration would apply to all on the same line. Thanks for the clarification!!

Dim lstCol As Long, lstRow As Long, dRow As Long

 

[Marc L]

Yes !​

​

Or just using the character data type declaration as you can find out within the VBA help of Long, sample in this post :​

​

Delete row under condition

Hey Guys, I'm going to start explaining this simple as possible, let me know if I don't make sense ! In my workbook there's sheet "A" and "B" If the value on the same row for column F and G is the same on sheet "A" and "B" then delete that row in sheet "A" Thanks !

www.mrexcel.com