PDA

View Full Version : Solved: Code Modify



KK1966
06-04-2008, 11:57 PM
Hi

I'm firstly to posting here's and want to help as I very newly to fact the VBA cdoe,

I have code below and have tyring many time to modify but not success! I want to base on the "Inputbox" value = file name to open the file,

Experts, Please help learn me about as show in RED area, which I want to base "INPUTBOX" value = file name.


code :
Sub testing()
Dim home As Object
Set home = ActiveWorkbook.ActiveSheet
Range("a5").select
Workbooks.Open("C:\Documents and Settings\sgg08920\Desktop\test.xls").Worksheets("Sheet1").Range("H13:H16").Select
Selection.Copy
home.Paste
Workbooks("test.xls").Close (False)

End Sub

Someone can help me modify the as RED base inputbox criteria to find the file name.

Your help I highly appreciate

Please

Bob Phillips
06-05-2008, 12:13 AM
This will browse for the file



Sub testing()
Dim home As Worksheet
Dim Filename As String
Dim Wb As Workbook

With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
If .Show = -1 Then

Filename = .SelectedItems(1)
Set home = ActiveWorkbook.ActiveSheet
Range("a5").Select
Set Wb = Workbooks.Open(Filename)
Wb.Worksheets("Sheet1").Range("H13:H16").Copy
home.Paste
Wb.Close (False)
End If
End With
End Sub

KK1966
06-05-2008, 12:21 AM
This will browse for the file



Sub testing()
Dim home As Worksheet
Dim Filename As String
Dim Wb As Workbook

With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
If .Show = -1 Then

Filename = .SelectedItems(1)
Set home = ActiveWorkbook.ActiveSheet
Range("a5").Select
Set Wb = Workbooks.Open(Filename)
Wb.Worksheets("Sheet1").Range("H13:H16").Copy
home.Paste
Wb.Close (False)
End If
End With
End Sub




Thanks ur great help

KK1966
06-05-2008, 12:50 AM
Daer Xld

One more aditional ask, if I want to copy again in the same opened file at Range A5 to objects workbook, sheets A3

how can it top Change your code:


Pleased And thanks very much

Bob Phillips
06-05-2008, 01:53 AM
I am not sure what you want to copy where.

KK1966
06-05-2008, 02:00 AM
Sorry about the unclear message

means
Wb.Worksheets("Sheet1").Range("H13:H16").Copy
home.Paste
then same worksheet to copy another section "T5:T9")
paste by active workbook ("E1")

how can it to modify the code ?

Please help help me

Bob Phillips
06-05-2008, 02:23 AM
Just add



Range("E1").Select
Wb.Worksheets("Sheet1").Range("T5:T9").Copy


before the close

KK1966
06-05-2008, 02:33 AM
I have try this, But just only pasted at the A5 only


Sub testing()
Dim home As Worksheet
Dim Filename As String
Dim Wb As Workbook

With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
If .Show = -1 Then

Filename = .SelectedItems(1)
Set home = ActiveWorkbook.ActiveSheet
Range("a5").Select
Set Wb = Workbooks.Open(Filename)
Wb.Worksheets("Sheet1").Range("H13:H16").Copy
home.Paste
Range("E1").Select
Wb.Worksheets("Sheet1").Range("T5:T9").Copy
home.Paste
Wb.Close (False)
End If
End With
End Sub



PLEASE HELP ME CHECK WHAT WAS I MADE A WORNG

Bob Phillips
06-05-2008, 02:51 AM
Try this version



Sub testing()
Dim home As Worksheet
Dim Filename As String
Dim Wb As Workbook

With Application.FileDialog(msoFileDialogOpen)

Set home = ActiveWorkbook.ActiveSheet
.AllowMultiSelect = False
If .Show = -1 Then

Filename = .SelectedItems(1)
Set Wb = Workbooks.Open(Filename)
Wb.Worksheets("Sheet1").Range("H13:H16").Copy home.Range("A5")
Wb.Worksheets("Sheet1").Range("T5:T9").Copy home.Range("E1")
Wb.Close (False)
End If
End With
End Sub

KK1966
06-05-2008, 02:56 AM
PERFECTLY,
THANK