• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA copy data to spreadsheet using a dynamic table as reference

D

dontakeshat

New Member
Kind of a weird question, but I need help with VBA in Excel. So I have three spreadsheets in the same workbook. Lets call them Source, Destination, and Table.

Source: Contains data I want to copy into the destination

Destination: Contains blank fields I want to fill using the table data from the source sheet

Table: contains a table that has two columns with headers (S, D). There is a letter in each row denoting which column matches where. I.E A2 is A and B2 is C. This shows where the data from column A in the source worksheet should be copied into column C in the Destination sheet. To further complicate this, the macro should be able to work if for example A2 is changed to B, then the B column from the source sheet should be copied into the C column in the destination sheet.

Any help at all is welcome and much appreciated.
 
Hany ali said:
where's the file ?!!!
Click to expand...
Here's a sample file, Hany. Thank you for the reply. Working with a great member from another forum, I have this code thusfar
Code: 
Option Explicit

Sub Destination()
Dim c As Range
Dim rng As Range
Dim i As Long, j As Long, x As Long
Sheet3.Cells(1, 1).CurrentRegion.ClearContents
Sheet3.Range("A1:B5").Value = Sheet1.Range("A1:B5").Value
Set rng = Sheet2.Range("B2:B4")
    For Each c In rng
        Sheet3.Cells(1, CStr(c)) = Sheet1.Cells(1, CStr(c.Offset(0, -1)))
    Next c
   
    For i = 2 To 5
        For j = 3 To 5
            x = Let2Nb(Application.WorksheetFunction.Index(Sheet2.Range("A1:A5"), Application.Match(Nb2Let(j), Sheet2.Range("B1:B5"), 0)))
            Sheet3.Cells(i, j) = Sheet1.Cells(i, x)
        Next j
    Next i

End Sub

Function Let2Nb(ColumnLetter As String) As Long
    Let2Nb = Range(ColumnLetter & 1).Column
End Function

Function Nb2Let(ColumnNumber As Long) As String
  Nb2Let = Split(Cells(1, ColumnNumber).Address, "$")(1)
End Function

But the actual file has many more columns in the source and destination sheet, so I would need something that I could extrapolate easily. The middle "Transfer Table" is the same with just more entries to match the added columns the actual file has. If necessary I could upload the actual file, but it has some sensitive information on it
 

Attachments

  • Test Cars.xlsx
    11.4 KB · Views: 3
dontakeshat
as well as
  • if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
You must log in or register to reply here.
Back
Top