How do I import same csv file to different tabs?

[mcrosman]

I'm using the first code below to import one file into one tab, and that works fine. But when I copy that same code and paste below it (altering Dim and sheetnames to new sheet of course) it won't run (I need to import the same csv file to multiple tabs). What am I doing wrong? I'm pretty new to VBA so I don't know what question to ask to correct this...


-- Original Code that works Properly--

Option Explicit


Sub CurrMonthMaterialMGMT()

'Dimensions


Dim sheetname2 As String

sheetname2 = "MGMTwk1"

Dim bireport As String

bireport = "MikeCathyPEToolReport.CSV"

Dim strpath As String

strpath = "usevtfs0730daystorageGlobal Analytics TeamMike and Cathy ReportBI Downloads"


Sheet2.Name = sheetname2

Sheet2.Cells.ClearContents


'Bring in BI Query

With Sheet2.QueryTables.Add(Connection:="TEXT;" & strpath & bireport, Destination:=Range("A1"))

.Name = Left(bireport, Len(bireport) - 4)

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 1252

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = True

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With


End Sub


--Code that I copy/paste/adjust and give me this error: "Run-time error '5': Invalid procedure call or argument"--


Option Explicit


Sub CurrMonthMaterialMGMT()

'Dimensions


Dim sheetname2 As String

sheetname2 = "MGMTwk1"

Dim sheetname3 As String

sheetname3 = "MGMTwk2"

Dim bireport As String

bireport = "MikeCathyPEToolReport.CSV"

Dim strpath As String

strpath = "usevtfs0730daystorageGlobal Analytics TeamMike and Cathy ReportBI Downloads"


Sheet2.Name = sheetname2

Sheet2.Cells.ClearContents

'Bring in BI Query

With Sheet2.QueryTables.Add(Connection:="TEXT;" & strpath & bireport, Destination:=Range("A1"))

.Name = Left(bireport, Len(bireport) - 4)

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 1252

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = True

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

Sheet3.Name = sheetname3

Sheet3.Cells.ClearContents


'Bring in BI Query

With Sheet3.QueryTables.Add(Connection:="TEXT;" & strpath & bireport, Destination:=Range("A1"))

.Name = Left(bireport, Len(bireport) - 4)

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 1252

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = True

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With


End Sub

 

[Luke M]

I believe this is the culprit line:

[pre]

With Sheet3.QueryTables.Add(Connection:="TEXT;" & strpath & bireport, Destination:=Range("A1"))
Note that the last Range object doesn't have a worksheet specified, so it defaults to whatever worksheet is currently active. Since you never changed active worksheets between sheet2 and sheet3, this causes a problem.

However, I have to ask, if it's the same data in each sheet, why not just copy everything from one tab to the next, rather than doing a CSV import?

Sheet2.Cells.Copy Sheet3.Cells

[/pre]

 

[mcrosman]

That was the culprit line! Thank You. I'm actually copying two different csv files, but I wanted to start with one to orient myself on how to pull two. Didn't want to bring two issue to the forum to start with. : )


Thank You again.

 

[Luke M]

You're welcome, glad I could help. =)