assign sheet for embedded macro to run in

A

Anders

Hi All,

I'm having trouble with the following macro. I need to get the "copy
milestones" to run in the file I open and paste into the worksheet I created.
Currently, the macro is copying from the newly created workbook. The sheet
I open will always have a different name so I can't refer to it by sheetname.
How do I make the embedded macro run on the file I select to open?

TIA,
Anders

Sub GetData44()
Set NewBook = Workbooks.Add
With NewBook
ActiveSheet.Name = "Milestone Exceptions"
End With
Set bswks = ActiveSheet
Dim str As String
str = Application.GetOpenFilename
Application.Run ("Copy_Milestones2")
bswks.Activate
Range("a1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

The "Copy Milestones" it refers to is the following:

Sub Copy_Milestones(
Range("A82:j119,A193:j230,A304:J341,A415:J452,A526:J563,A637:J674,A748:J785,A859:J896,A970:J1007,A1081:J1118,A1192:J1229,A1303:J1340,A1411:J1451,A1525:J1562").Copy
End Sub
 
J

Jacob Skaria

Hi Anders

Modified your macro a bit. Try the below and feedback. You can combine the
procedures...

Sub GetData44()
Dim ws As Worksheet, strFile As String
Dim wb1 As Workbook, wb2 As Workbook

Set wb1 = Workbooks.Add
Set ws = wb1.ActiveSheet
ws.Name = "Milestone Exceptions"

strFile = Application.GetOpenFilename
Set wb2 = Workbooks.Open(strFile)
Call Copy_Milestones(wb2, ws)
End Sub

Sub Copy_Milestones(wbBook As Workbook, ws As Worksheet)
wbBook.ActiveSheet.Range("A82:j119,A193:j230,A304:J341," & _
"A415:J452,A526:J563,A637:J674,A748:J785,A859:J896,A970:J1007," & _
"A1081:J1118,A1192:J1229,A1303:J1340,A1411:J1451,A1525:J1562").Copy _
ws.Range("A1")
End Sub


If this post helps click Yes
 
A

Anders

Jacob - very nice!

Follow up - my next step is to open another file open box, select another
file, run the "copy milestones" to select the same and paste in the
previously created workbook starting in cell k1. End goal is to compare the
two side by side. I tried replicating your code, but couldn't get it to
paste into the proper sheet. I think I need to replicate the copy_milestones
code - change the paste destination cell...then..activate the original sheet
but I couldn't get it. thoughts?

Once it's on the sheet - that's my other post on hiding the rows.

Huge thanks..

Anders
 
J

Jacob Skaria

Hi Anders

Modified the copy procedure to suit your requirement. Instead of the
destination sheet the destination range is passed as an argument so that you
can reuse..

Sub GetData44()
Dim ws As Worksheet, strFile As String
Dim wb1 As Workbook, wb2 As Workbook

Set wb1 = Workbooks.Add
Set ws = wb1.ActiveSheet
ws.Name = "Milestone Exceptions"

strFile = Application.GetOpenFilename
Set wb2 = Workbooks.Open(strFile)
Call Copy_Milestones(wb2, ws.Range("A1"))

strFile = Application.GetOpenFilename
Set wb2 = Workbooks.Open(strFile)
Call Copy_Milestones(wb2, ws.Range("K1"))

End Sub

Sub Copy_Milestones(wbBook As Workbook, rngTemp As Range)
wbBook.ActiveSheet.Range("A82:j119,A193:j230,A304:J341," & _
"A415:J452,A526:J563,A637:J674,A748:J785,A859:J896,A970:J1007," & _
"A1081:J1118,A1192:J1229,A1303:J1340,A1411:J1451,A1525:J1562").Copy _
rngTemp
End Sub


If this post helps click Yes
 
A

Anders

Jacob - Absolutely beautiful. I added some msgbox's for user instructions on
what files to select and it's perfect.

Thanks so much.

Anders
 
Top