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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
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 …​
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,944
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
Yes !​
Or just using the character data type declaration as you can find out within the VBA help of Long, sample in this post :​
 

Forum statistics

Threads
1,148,195
Messages
5,745,285
Members
423,942
Latest member
excelhelp1423

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
Top