Copy cells from a excel table to another excel spreadsheet

Question

This is probably going to sound confusing so I'll try my best to explain. I'm really new to VBA and I am ok at excel. I am trying to make a spreadsheet that fills in certain values into another spreadsheet that is setup as a template. However there are multiple different templates but that need to be filled in depending on the value found. The templates are separate files. Without been able to give away client business information I will use an example. I have template a.xls, b.xls and c.xls, within these there are 3 cells that need information from the setup spreadsheet, ie, result1, result2, result3 from the table. So if "a" is found then then take result1, result2 and result3 and copy them and paste into a.xls the requires cells.

The code I have at the moment finds the values in the Data column and just returns a msgbox. I don't know if this is even the right way around things and I'm stumbling through examples online so any help would be awesome.

This is my code so far.

    Sub LookupTableValue()
Dim Table2 As ListObject
Dim FoundCell1, FoundCell2 As Range
Dim LookupValue1, LookupValue2 As String
  LookupValue1 = "a"
  LookupValue2 = "b"
  Set Table2 = ActiveSheet.ListObjects("Table2")
    Set FoundCell1 = Table2.DataBodyRange.Columns(1).Find(LookupValue1, LookAt:=xlWhole)
    Set FoundCell2 = Table2.DataBodyRange.Columns(1).Find(LookupValue2, LookAt:=xlWhole)
    If Not (FoundCell1 Is Nothing) Then
        If FoundCell1.Value = LookupValue1 Then
            MsgBox "Found a"
        End If
    End If
    If Not (FoundCell2 Is Nothing) Then
        If FoundCell2.Value = LookupValue2 Then
            MsgBox "Found b"
        End If
    End If
End Sub

So I guess what I want instead of the MsgBox line is an open the specific xls spreadsheet that is the template and then copy the cells from the table into this template, and then saveas new .xls in a different folder.

score 0 · Answer 1 · answered Jan 13 '20 at 07:13

Pathname=File Path
If FoundCell1.Value = LookupValue1 Then
            MsgBox "Found a"
            Filename=File name depending on the value
            Set workbook = Workbooks.Open(Filename:=Pathname & Filename)
            Copy table content to above workbook. See this [https://stackoverflow.com/questions/31702885/copy-tables-to-another-worksheet][1]
            workbook.SaveAs Filename:=NewFileName, FileFormat:=xlWorkbookNormal
End If

Hope this will help you.

Copy cells from a excel table to another excel spreadsheet

1 Answers1