G
G. O'Grady
I'm writing a basic macro to do following:
* Have a primary workbook that has a data tab used in a pivot in same
workbook which is where macro will be executed from
* Have a second workbook that has data that is copied and pasted onto
data tab in primary workbook
* After copying and pasting data from second to primary, want to close
the second workbook and leave cursor on updated pivot tab in primary
workbook
3 questions:
1. Macro is good from standpoint of allowing user to browse and
select file and open copy copy data but fails in that the next line of
code is: Windows ("actualfilename.xlsm"). activate ... The minute
this file is saved as anything else, the macro will fail as the name
will be different than what is stored. How can I make the macro
flexible?
2. The other place where I need help is in closing the second
workbook. I had already used filename when allowing user to browse
and select the file. So I was hoping that is stored and thus
something I could use in my code. It wasn't working so I removed but
am hoping someone can help me figure out how to incorporate.
3. The second file will have varying rows over time (first time I
executed, there were 37 rows) ... the second time, it was double
however the copy/paste only brought in the 37 rows. How can I make
sure copy is always of all info?
Below is the VBA as is right now if that helps...
Thanks to any advice
Gretchen
VBA CODE:
Sub GetARMSExport()
Application.ScreenUpdating = False
Dim Finfo As String
Dim FilterIndex As Integer
Dim title As String
Dim filename As Variant
'set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & "Lotus Files (*.prn),*.prn,"
& "Comma Separted Files (*.csv),*.csv," & "ASCII Files (*.asc),*.asc,"
& "All Files (*.*),*.*"
'Display *.* by default
FilterIndex = 5
'Set the dalog box caption
title = "Select a File to Import"
'Get the filename
filename = Application.GetOpenFilename(Finfo, _
FilterIndex, title)
'Handle return info from dialog box
If filename = False Then
MsgBox "No file was selected."
Else
Workbooks.Open filename
Cells.Select
Selection.Copy
Windows("B2A_E2C Tool.fy2012 - revised draft unprotected - 021512
v4.xlsm"). _
Activate
Sheets("ARMS Detailed Scheduling Report").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("ARMS Summary Scheduled Hrs").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Calculate
Range("A6").Select
Application.ScreenUpdating = True
End If
End Sub
* Have a primary workbook that has a data tab used in a pivot in same
workbook which is where macro will be executed from
* Have a second workbook that has data that is copied and pasted onto
data tab in primary workbook
* After copying and pasting data from second to primary, want to close
the second workbook and leave cursor on updated pivot tab in primary
workbook
3 questions:
1. Macro is good from standpoint of allowing user to browse and
select file and open copy copy data but fails in that the next line of
code is: Windows ("actualfilename.xlsm"). activate ... The minute
this file is saved as anything else, the macro will fail as the name
will be different than what is stored. How can I make the macro
flexible?
2. The other place where I need help is in closing the second
workbook. I had already used filename when allowing user to browse
and select the file. So I was hoping that is stored and thus
something I could use in my code. It wasn't working so I removed but
am hoping someone can help me figure out how to incorporate.
3. The second file will have varying rows over time (first time I
executed, there were 37 rows) ... the second time, it was double
however the copy/paste only brought in the 37 rows. How can I make
sure copy is always of all info?
Below is the VBA as is right now if that helps...
Thanks to any advice
Gretchen
VBA CODE:
Sub GetARMSExport()
Application.ScreenUpdating = False
Dim Finfo As String
Dim FilterIndex As Integer
Dim title As String
Dim filename As Variant
'set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & "Lotus Files (*.prn),*.prn,"
& "Comma Separted Files (*.csv),*.csv," & "ASCII Files (*.asc),*.asc,"
& "All Files (*.*),*.*"
'Display *.* by default
FilterIndex = 5
'Set the dalog box caption
title = "Select a File to Import"
'Get the filename
filename = Application.GetOpenFilename(Finfo, _
FilterIndex, title)
'Handle return info from dialog box
If filename = False Then
MsgBox "No file was selected."
Else
Workbooks.Open filename
Cells.Select
Selection.Copy
Windows("B2A_E2C Tool.fy2012 - revised draft unprotected - 021512
v4.xlsm"). _
Activate
Sheets("ARMS Detailed Scheduling Report").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("ARMS Summary Scheduled Hrs").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Calculate
Range("A6").Select
Application.ScreenUpdating = True
End If
End Sub