Problems with moving to next empty column

[BaggieDan]

Hi all,

I have a form that different users fill in, the results are the collected
and presented in a number of ways. I have sorted the form and want the macro
to select the necessary cells and paste them into a master workbook. There
will be a large number of forms so I have, will the help a previous post, put
together a macro that allows the user to select multiple workbooks, the
master worksheet then checks a cell to see which worksheet to paste the
information to, selects, copy and pastes the appropriate cells, closes the
form and opens the next. The code below does this however when it comes to
pasting the information it does something strange.

If you select, for instance, 3 forms to open from the file window, e.g. A, B
and C the macro will run and paste A into Worksheet A of the master workbook,
B into B etc, the problem is that it pastes C once, B twice and A three
times. I think it is probably something to do with the NextColumn line but
I am at a bit of lose as to what. Can anyone help?

I need to be able to select the Next empty column so I can put multiple
forms on one workbook.

X is the workbook name of the open form, it is contained in each form the
user fills in.

Y is the name of the appropriate worksheet, it is also contained in each form.

I know I could streamline the code but I just want this bit to work first!

The code is below :

Public Sub ProcessAllFiles()
'
' Macro1 Macro
'
' Input Boxes and File Name Selection
Dim varFileList As Variant
Dim IngFileCount As Long
Dim ilngFileNumber As Long
Dim strFileName As String
varFileList = Application.GetOpenFilename(FileFilter:="Excel
Files(*.xls),*.xls", Title:="Open Excel File(s)", MultiSelect:=True)
IngFileCount = FileCount(varFileList)
If IngFileCount = 0 Then GoTo ExitSub
' User canceled out of dialog box.
For ilngFileNumber = 1 To IngFileCount
Workbooks.Open Filename:=CurrentFileName(varFileList, ilngFileNumber)


' Workout which form to which worksheet
x = Range("A1")
y = Range("A4")
If y = "This" Then GoTo This
If y = "Has" Then GoTo Has
If y = "Worked" Then GoTo Worked



' Copy and Paste
This:
Application.ScreenUpdating = False
Workbooks(x).Activate
Range("A1").Select
Selection.Copy
Workbooks("Test.xls").Activate
Worksheets(y).Activate
Nextcolumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1
Cells(1, Nextcolumn).Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks(x).Activate
Has:
Application.ScreenUpdating = False
Workbooks(x).Activate
Range("A1").Select
Selection.Copy
Workbooks("Test.xls").Activate
Worksheets(y).Activate
Nextcolumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1
Cells(1, Nextcolumn).Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks(x).Activate
Worked:
Application.ScreenUpdating = False
Workbooks(x).Activate
Range("A1").Select
Selection.Copy
Workbooks("Test.xls").Activate
Worksheets(y).Activate
Nextcolumn = Application.WorksheetFunction.CountA(Range("1:1")) + 1
Cells(1, Nextcolumn).Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks(x).Activate

ActiveWorkbook.Close
Next ilngFileNumber
ExitSub:
End Sub




----------------------------------------------------------------------------------------------------------------------------

Private Function FileCount(varFileList) As Long
Select Case VarType(varFileList)
Case vbBoolean
'User canceled out of the File Open Dialog box.
FileCount = 0
Case vbString
'Dialog box is in single file mode.
'Single file selected for opening only.
FileCount = 1
Case vbArray + vbVariant
'Multiple files selected for processing.
FileCount = UBound(varFileList) - LBound(varFileList) + 1
End Select
End Function

----------------------------------------------------------------------------------------------------------------------------
Private Function CurrentFileName(varFileList As Variant, ilngFileNumber As
Long) As String
Select Case VarType(varFileList)
Case vbBoolean
'User canceled out of the File Open dialog box.
CurrentFileName = ""
Case vbString
'Dialog box is in single file mode.
'Single file selected for opening only.
CurrentFileName = varFileList
Case vbArray + vbVariant
'Multiple files selected for processing.
'Return the filename currently pointed to.
CurrentFileName = CStr(varFileList(ilngFileNumber))
End Select
End Function



I think the above Multiple File selection code was written by Bill Renaud
(Many Thanks!!!!)

 

[John_John]

There is a better way to find the next blank cell in a row:

NextColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If NextColumn > 1 Then NextColumn = NextColumn + 1

I hope it helps.


Ο χÏήστης "BaggieDan" έγγÏαψε:

 

[JLGWhiz]

I would get rid of the GoTo and just use the conditional criteria in an
If...Then...ElseIf algoritm. By using the GoTo with labels and the way it is
written, there is nothing to prevent the code from running for "has" and
"worked" if you GoTo "This". The label does not prevent the code from
running. It is ignored except when you use the GoTo. Walk through the code
line by line using F8 and you will see it happen.