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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.