CODEWITHSUNDEEP
excelvba
user3140168
user3140168

Reputation: 21

VBA data transfer from one workbook to another, data type mismatch error

I'm new to VBA, but learning. I've written most of the following code myself, some of it was inherited. My goal here is to loop through multiple text files (these each contain a unique set of raw data) and copy (or in some other way transfer) that data into an analysis template that I've made which will then be "saved as" with the same filename as the raw data text file. I've been working on this for several days and have done a significant amount of searching to get this far, however, I'm currently stuck with a "Run-time type '13' error - mismatch data type" that I don't understand so I don't know how to get past it. The error is @ "Data.Sheets(Sheet1).Range("A1:G180000").Copy. If I comment out the aforementioned line and the one that follows it and use the line above ("Template.Sheets(Sheet1).Range("A1:G180000").Value...") I still get the same error. My code is posted below and any help is very much appreciated. Thanks :)

Sub Shift_Load_Data_Plotter_Template()

'Josh Smith
'12/27/2013
'Shift Load Data Plotter Template
'This macro will bring up the Open dialog box so you can open multiple text files and analyze them using the Shift Load Data Plotter Template

'Brings up the Open window so you can select multiple excel files to import
    Dim fn As Variant, f As Integer
    Dim FileName As String
    'Data is the source workbook and Template is the destination workbook
    Dim Data As Workbook
    Dim Template As Workbook

    fn = Application.GetOpenFilename("Text files,*.txt", _
    1, "Select One Or More Files To Open", , True)
    If TypeName(fn) = "Boolean" Then Exit Sub
    'the line below was modified from from just "workbooks.open "Z:\..." to "Set Template = Workbooks.open..."
    'opens the Shift Load Data Analyzer Template workbook and sets the "Template" variable equal to said workbook
    Set Template = Workbooks.Open("Z:\General Reference, Tools\Shift Load Data Analyzer Template.xlsx")


    For f = 1 To UBound(fn)
    'the line below was modified from just "workbooks.open fn(f)" to what it shows now
    'sets the "Data" variable equal to the workbook which contains the data from the text file
    Set Data = Workbooks.Open(fn(f))
    FileName = ActiveWorkbook.Name
    'Data.Activate



    'Template.Sheets(Sheet1).Range("A1:G180000").Value = Data.Sheets(Sheet1).Range("A1:G180000").Value

    Data.Sheets(Sheet1).Range("A1:G180000").Copy
    Template.Sheets(Sheet1).Range("A1").PasteSpecial (xlPasteValues)


    'the line below used to be "ActiveWorkbook.SaveAs..."
    Template.SaveAs FileName:="Z:\" & FileName & ".xlsx"

    Data.Close


    Next f

End Sub

Upvotes: 2

Views: 856

Answers (2)

mrtgold
mrtgold

Reputation: 61

Try changing it to:

    Data.Sheets(1).Range("A1:G180000").Copy
    Template.Sheets(1).Range("A1").PasteSpecial (xlPasteValues)

Upvotes: 0

philorube
philorube

Reputation: 155

The line:

Data.Sheets(Sheet1).Range("A1:G180000").Copy

Should probably read as follows:

Data.Sheets("Sheet1").Range("A1:G180000").Copy

You need quotation marks around the sheet name if you're referring to the name (the Sheets() function is looking for the sheet name you see on the tab in Excel, not the Sheet1, Sheet2, Sheet3, etc. you see in the VBA screen). Otherwise you could write it like this:

Data.Sheet1.Range("A1:G180000").Copy

Upvotes: 1

Related Questions