Modify Side by side data to one below the other for every 6

June 26, 2014 at 03:21:49
Specs: Windows XP sp3
I have VBS that works find for consolidate multiple file data to one Sheet, But consolidation is side by side. I want one below other. The VBS is:-
Option Explicit

Sub ImportCSVsWithReference()

Dim wbCSV As Workbook
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("MasterCSV")
Dim fPath As String: fPath = "C:\Data\Import"
Dim fCSV As String
Dim NextCol As Long

If MsgBox("Clear the existing MasterCSV sheet before importing?", _
vbYesNo, "Clear?") = vbYes Then
NextCol = 1
NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
End If

Application.ScreenUpdating = False

fCSV = Dir(fPath & "*.csv")

Do While Len(fCSV) > 0
'open a CSV file
Set wbCSV = Workbooks.Open(fPath & fCSV)
'insert row 1 and add CSV name
Rows(1).Insert xlShiftDown
Range("A1") = ActiveSheet.Name
'copy date into master sheet and close source file
ActiveSheet.UsedRange.Copy wsMstr.Cells(1, NextCol)
wbCSV.Close False
'ready next CSV
fCSV = Dir
NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1

Application.ScreenUpdating = True
End Sub


See More: Modify Side by side data to one below the other for every 6

June 26, 2014 at 05:11:53
I will work on your question when I have access to a computer later today, but first I would like to point out 2 items, and then ask a question.

1 - You said I have VBS...

VBS stands for Visual Basic Script. The code you posted was written in VBA, or Visual Basic for Applications. See here for the differences:

2 - Please click on the blue line at the end of this post and read the instructions on how to post VBA code so that it is easier for us to read. Using the pre tags will retain the indenting that you probably see when you use the VBA editor.

Now for my question....

Your subject line ends with for every 6

I don't see anything in the code that relates to "every 6”. What does "every 6" have to do with the code?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question