Bruce T
10-13-2009, 07:32 AM
Hi There,
hope someone can help. Basically most of my macros are given to a user who then clicks a button to execute it. The button executes a macro by giving the user the standard MS Excel browse box. User then picks correct workbook he/she wants processed. Macro then opens that workbook copies the relevant sheet's data and then pastes into a new sheet on the macro.xls file. This then allows vlookups etc to give the user the correct data on a summary sheet without changing the original data sheet. The problem is the syntax I normally use seems to only work only erratically now.
e.g.
Dim aaaa_bbbb As Variant
aaaa_bbbb = Application.GetOpenFilename("Latest ----(-- New Spreadsheet) (*.xls), *.xls", , _
"Select Current -- Report", , False)
If aaaa_bbbb = False Then
MsgBox "Require Current ----- to continue - process exiting."
Exit Sub
End If
Workbooks.Open aaaa_bbbb
n = 1
Do Until Left(Sheets(n).Name, 4) = "cccc"
n = n + 1
Loop
Sheets(n).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
'need to remove filters aaaa_bbbb
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
'Reselect all data to copy and paste it aaaa_bbbb
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("------- Macro.xls").Activate
Sheets("dddd").Select
Range("A1").Select
' Windows("------ Macro.xls").Sheets("dddd").Cells("A1").Paste
ActiveSheet.Paste
Could somebody advise where I am going wrong. Is it possibly aaaa_bbbb is a variant and I am treating it like an object? If there is a better or standard way of doing this could somebody let me have a copy? I've simplified what I've pasted and also changed names because of company data policy also normally I am selecting and using more than one spreadsheet (workbook) as the data to be processed
Bruce T
hope someone can help. Basically most of my macros are given to a user who then clicks a button to execute it. The button executes a macro by giving the user the standard MS Excel browse box. User then picks correct workbook he/she wants processed. Macro then opens that workbook copies the relevant sheet's data and then pastes into a new sheet on the macro.xls file. This then allows vlookups etc to give the user the correct data on a summary sheet without changing the original data sheet. The problem is the syntax I normally use seems to only work only erratically now.
e.g.
Dim aaaa_bbbb As Variant
aaaa_bbbb = Application.GetOpenFilename("Latest ----(-- New Spreadsheet) (*.xls), *.xls", , _
"Select Current -- Report", , False)
If aaaa_bbbb = False Then
MsgBox "Require Current ----- to continue - process exiting."
Exit Sub
End If
Workbooks.Open aaaa_bbbb
n = 1
Do Until Left(Sheets(n).Name, 4) = "cccc"
n = n + 1
Loop
Sheets(n).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
'need to remove filters aaaa_bbbb
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
'Reselect all data to copy and paste it aaaa_bbbb
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("------- Macro.xls").Activate
Sheets("dddd").Select
Range("A1").Select
' Windows("------ Macro.xls").Sheets("dddd").Cells("A1").Paste
ActiveSheet.Paste
Could somebody advise where I am going wrong. Is it possibly aaaa_bbbb is a variant and I am treating it like an object? If there is a better or standard way of doing this could somebody let me have a copy? I've simplified what I've pasted and also changed names because of company data policy also normally I am selecting and using more than one spreadsheet (workbook) as the data to be processed
Bruce T