Hi All, Am new to this forum and also not a VBA expert. I written a code to open a workbook(namely X) to copy data and paste it into the workbook(namely Y) where my macro is running. Here i want to edit few lines in the X workbook before copying into my Y workbook. Please help me with code to stop the macro and resume it once i complete my edit work.
Thanks in advance.
Regards,
Karthikha
What code do you have so far?
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Hi Mike, Here is my code...
Sub copy()
Dim vFile As Variant
Dim strdir as string
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
strDir = "C:\
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If
“Here I need a code to stop the macro to remove filters on vfile manually and then to continue macro.
Note:Vfile is a shared file with password protection.”
wsCopyFrom.Range("B6:E12").Copy
wsCopyTo.Range("B5").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wbCopyFrom.Close SaveChanges:=False
End Sub
Last edited by karthikha; 11-25-2017 at 11:01 AM.
Please Login or Register to view this content.
Hi Daboho, Thanks for your reply.....Actually copy is not an issue for me.....i want to stop the macro and after removing filters manually, i like to run the balance code..... i am looking for code to stop,edit and then to continue the macro.
Hi Mike, Here is my code...
Sub copy()
Dim vFile As Variant
Dim strdir as string
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
strDir = "C:\
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If
“Here I need a code to stop the macro to remove filters on vfile manually and then to continue macro.
Note:Vfile is a shared file with password protection.”
wsCopyFrom.Range("B6:E12").Copy
wsCopyTo.Range("B5").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wbCopyFrom.Close SaveChanges:=False
End Sub
Hi All...I found a work around.....Thanks...
Sub copy()
Dim vFile As Variant
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
strdir = "D:\"
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
End If
End Sub
Sub closee()
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
Set wbCopyFrom = Workbooks(2)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
wsCopyFrom.Range("a2:p1600").copy
wsCopyTo.Range("a2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wbCopyFrom.Close SaveChanges:=False
Worksheets(1).Range("i2").copy
ActiveSheet.Range("i3:i1600").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Range("i2").CurrentRegion.Select
Selection.Sort Key1:=Range("i2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Last edited by karthikha; 11-26-2017 at 05:44 AM.
MID(A9,FIND(", Hrs: ",A9)+LEN(", Hrs: "), FIND(", Mins: ",A9)-FIND(", Hrs: ",A9)-LEN(", Hrs: "))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks