VBA Help

[ionelz]

Hi,
I have this simple VBA to Import one Excel file into another.
I would like to add a modification here :
FileSource.Worksheets("Sheet1").Range("b1 :b500").Copy

If M2="option 1" then
FileSource.Worksheets("Sheet1").Range("b1 :b500").Copy
If M2="option 2" then
FileSource.Worksheets("Sheet1").Range("a1 :a500").Copy

Private Sub CommandButton1_Click()
Dim FileFolder As FileDialog
Dim FileName As String
Dim FileSource As Workbook
Dim desWS As Worksheet

On Error GoTo myerror
Set desWS = ActiveSheet

Range("C4:B500").ClearContents

Set FileFolder = Application.FileDialog(msoFileDialogFilePicker)
FileFolder.Title = "Please Select a folder And file."

If FileFolder.Show = -1 Then

FileName = FileFoIder.SelectedItems(1)

Set FileSource = Workbooks.Open(FileName, 0, True)

FileSource.Worksheets("Sheet1").Range("bl :b500").Copy
desWS.Range("B4").PasteSpeciaI xlPasteVaIues

Else
'cancel pressed

End If

myerror:
Application.CutCopyMode = False
If Not FileSource Is Nothing Then FileSource.Close False

 

[frabulator]

It would make it a lot easier to read if you could put your code in brackets. You can do that with the "</>" icon at the top of the post box.

Private Sub CommandButton1_Click()
Dim FileFolder As FileDialog
Dim FileName As String
Dim FileSource As Workbook
Dim desWS As Worksheet

On Error GoTo myerror
Set desWS = ActiveSheet

Range("C4:B500").ClearContents

Set FileFolder = Application.FileDialog(msoFileDialogFilePicker)
FileFolder.Title = "Please Select a folder And file."

If FileFolder.Show = -1 Then

FileName = FileFoIder.SelectedItems(1)

Set FileSource = Workbooks.Open(FileName, 0, True)

FileSource.Worksheets("Sheet1").Range("bl :b500").Copy
desWS.Range("B4").PasteSpeciaI xlPasteVaIues

Else
'cancel pressed

End If

myerror:
Application.CutCopyMode = False
If Not FileSource Is Nothing Then FileSource.Close False


End Sub

But, my question is, what is M2 referencing? Is it a string, is it a boolean, a checkbox, a combobox?

 

[ionelz]

M2 is Data Validation, with 2 options : Option 1 and Option 2
Data is STRING (TEXT)

 

[frabulator]

Well, you will need to somehow pass this along through the code. I would assume that it has been declared as public.

Though, I would suggest that it would be either an integer (1, 2, 3, etc) or a boolean because it is just two options. The reason is because a string has to be exactly the same. "Option1" is different than "option 1" or even "Option 1". However, 1 is always 1, and True is always True. See what I mean?

BUT, making it exactly as you requested is below. You will need to have M2 declared Public and as a String somewhere outside of a subroutine and then called to be changed to your option BEFORE the button is clicked.

Private Sub CommandButton1_Click()
Dim FileFolder As FileDialog
Dim FileName As String
Dim FileSource As Workbook
Dim desWS As Worksheet

On Error GoTo myerror
Set desWS = ActiveSheet

Range("C4:B500").ClearContents

Set FileFolder = Application.FileDialog(msoFileDialogFilePicker)
FileFolder.Title = "Please Select a folder And file."

If FileFolder.Show = -1 Then

FileName = FileFoIder.SelectedItems(1)

Set FileSource = Workbooks.Open(FileName, 0, True)

If M2 = "option 1" then
 FileSource.Worksheets("Sheet1").Range("b1 :b500").Copy
 elseIf M2 = "option 2" then
 FileSource.Worksheets("Sheet1").Range("a1 :a500").Copy
end if

desWS.Range("B4").PasteSpeciaI xlPasteVaIues

Else
'cancel pressed

End If

myerror:
Application.CutCopyMode = False
If Not FileSource Is Nothing Then FileSource.Close False


End Sub