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

mir994stan

New Member
Joined
Jul 18, 2021
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try this on a copy of your workbook

VBA Code:
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
 
Upvote 0
Solution
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 …​
 
Upvote 0
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.

VBA Code:
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
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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 !​
VBA Code:
   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
 
Upvote 0
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!!

VBA Code:
Dim lstCol As Long, lstRow As Long, dRow As Long
 
Upvote 0
Yes !​
Or just using the character data type declaration as you can find out within the VBA help of Long, sample in this post :​
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top