Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro-Variable File Name & Data Dump On Blank Row

Status
Not open for further replies.
DrMingle

DrMingle

Technical User
May 24, 2009
116
US
Help....!!!

("Visual Audit_ Cassie Waltz 6-18-09 98%.xls") I need to make this line of code whatever file I happen to have open...since the files change all the time...I need it to be variable.

The other thing is that I need to make this where the new data dumbs on the next blank row under the existing date.

Thanks in advance for any help.

Below is the recorded Macro:

Sub Attempt2()
'
' Attempt2 Macro
' Macro recorded 6/19/2009 by drmingle
'

'
Windows("Visual Audit_ Cassie Waltz 6-18-09 98%.xls").Activate
Range("B75:B84").Select
Selection.Copy
Windows("Brigman-Hannah - Audits - 2009.xls").Activate
Range("E1491").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Visual Audit_ Cassie Waltz 6-18-09 98%.xls").Activate
Range("C75:C84").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Brigman-Hannah - Audits - 2009.xls").Activate
Range("B1491").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.WindowState = xlMinimized
Windows("Visual Audit_ Cassie Waltz 6-18-09 98%.xls").Activate
Range("D75:I84").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
Windows("Brigman-Hannah - Audits - 2009.xls").Activate
Range("H1491").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Visual Audit_ Cassie Waltz 6-18-09 98%.xls").Activate
Range("B60").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
Windows("Brigman-Hannah - Audits - 2009.xls").Activate
Range("A1491").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1491:A1500"), Type:=xlFillDefault
Range("A1491:A1500").Select
Range("C1490").Select
Selection.AutoFill Destination:=Range("C1490:C1500"), Type:=xlFillDefault
Range("C1490:C1500").Select
Windows("Visual Audit_ Cassie Waltz 6-18-09 98%.xls").Activate
Range("B70").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("Brigman-Hannah - Audits - 2009.xls").Activate
Range("D1491").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D1491:D1500"), Type:=xlFillDefault
Range("D1491:D1500").Select
Windows("Visual Audit_ Cassie Waltz 6-18-09 98%.xls").Activate
Range("B71").Select
Selection.Copy
Windows("Brigman-Hannah - Audits - 2009.xls").Activate
Range("F1491").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F1491:F1500"), Type:=xlFillCopy
Range("F1491:F1500").Select
Windows("Visual Audit_ Cassie Waltz 6-18-09 98%.xls").Activate
Range("B72").Select
Selection.Copy
Windows("Brigman-Hannah - Audits - 2009.xls").Activate
Range("G1491").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G1491:G1500"), Type:=xlFillCopy
Range("G1491:G1500").Select
End Sub
 
Sort by date Sort by votes


Hi,

I avoid using the Activate and Select methods...
Code: 
    Dim wbAudits As Workbook
    
    Set wbAudits = Workbooks("Brigman-Hannah - Audits - 2009.xls")
    
    'I really do not recommend using activesheet.
    'rather reference an ACTUAL sheet!!!
    With ActiveSheet
        .Range("B75:B84").Copy
        'I really do not recommend using activesheet.
        'rather reference an ACTUAL sheet!!!
        wbAudits.ActiveSheet.Range("E1491").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
        
        .Range("C75:C84").Copy
        'I really do not recommend using activesheet.
        'rather reference an ACTUAL sheet!!!
        bAudits.ActiveSheet.Range("B1491").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
    
    '....etc.
    End With
    Set wbAudits = Nothing

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I like the feature: Set wbAudits = Nothing.

I changed things up a bit when it comes to adding data to the next empty row.

Currently this is spitting data all over the place...

Sub Attempt2()
'
' Attempt2 Macro
' Macro recorded 6/19/2009 by drmingle
'
Dim wbAudits As Workbook

Set wbAudits = Workbooks("Brigman-Hannah - Audits - 2009.xls")

ActiveSheet.Activate
Range("B75:B84").Select
Selection.Copy
wbAudits.Activate
Range("E1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Activate
Range("C75:C84").Select
Application.CutCopyMode = False
Selection.Copy
wbAudits.Activate
Range("B1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.WindowState = xlMinimized
ActiveSheet.Activate
Range("D75:I84").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
wbAudits.Activate
Range("H1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Activate
Range("B60").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
wbAudits.Activate
Range("A1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1491:A1500"), Type:=xlFillDefault
Range("A1491:A1500").Select
Range("C1490").Select
Selection.AutoFill Destination:=Range("C1490:C1500"), Type:=xlFillDefault
Range("C1490:C1500").Select
ActiveSheet.Activate
Range("B70").Select
Selection.Copy
Application.WindowState = xlMinimized
wbAudits.Activate
Range("D1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D1491:D1500"), Type:=xlFillDefault
Range("D1491:D1500").Select
ActiveSheet.Activate
Range("B71").Select
Selection.Copy
wbAudits.Activate
Range("F1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F1491:F1500"), Type:=xlFillCopy
Range("F1491:F1500").Select
ActiveSheet.Activate
Range("B72").Select
Selection.Copy
wbAudits.Activate
Range("G1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G1491:G1500"), Type:=xlFillCopy
Range("G1491:G1500").Select

Set wbAudits = Nothing

End Sub
 
Upvote 0 Downvote



Well you seemed to have ignored the bulk of my suggestions.

Of course, things will go "all over the place" if you do not have the proper SHEET reference, which you have TOTALLY disregarded.


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I apologize...

I must have misunderstood.

I have multiple files that I have to use this macro with...as far as I know (which my knowledge is limited with macros) I need a generic sheet name reference since all sheets will be different each time and every time...

Any additional help would be greatly appreciated...

 
Upvote 0 Downvote



ANY VBA procedure that deals with multiple workbooks or sheets, needs to have adequate references in order for the program to know WHAT sheet goes with WHAT range. Every range needs a sheet reference, and if multiple workbooks, workbook references as well.

You're going to have to figure out the LOGIC for how that can be accomplished in your application.

Otherwise, "all over the place" and unresolved reference errors.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I'm hopeful that the below structure is what you are talking about:

Sub Attempt2()
'
' Attempt2 Macro
' Macro recorded 6/19/2009 by drmingle

Dim wsSource As Worksheet
Dim wsDest As Worksheet
Set wsSource = ActiveSheet
Set wsDest = Windows("Brigman-Hannah - Audits - 2009.xls").ActiveSheet

wsSource.Activate
Range("B75:B84").Select
Selection.Copy
wsDest.Activate
Range("E1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wsSource.Activate
Range("C75:C84").Select
Application.CutCopyMode = False
Selection.Copy
wsDest.Activate
Range("B1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.WindowState = xlMinimized
wsSource.Activate
Range("D75:I84").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
wsDest.Activate
Range("H1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wsSource.Activate
Range("B60").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
wsDest.Activate
Range("A1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1491:A1500"), Type:=xlFillDefault
Range("A1491:A1500").Select
Range("C1490").Select
Selection.AutoFill Destination:=Range("C1490:C1500"), Type:=xlFillDefault
Range("C1490:C1500").Select
wsSource.Activate
Range("B70").Select
Selection.Copy
Application.WindowState = xlMinimized
wsDest.Activate
Range("D1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D1491:D1500"), Type:=xlFillDefault
Range("D1491:D1500").Select
wsSource.Activate
Range("B71").Select
Selection.Copy
wsDest.Activate
Range("F1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F1491:F1500"), Type:=xlFillCopy
Range("F1491:F1500").Select
wsSource.Activate
Range("B72").Select
Selection.Copy
wsDest.Activate
Range("G1").Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G1491:G1500"), Type:=xlFillCopy
Range("G1491:G1500").Select

Set wsDest = Nothing

End Sub
 
Upvote 0 Downvote
It is doing what you expect?

As I previously stated, I do not recommend using the Activate and Select methods. But what they accomplish for your macro, is that range references assume the active sheet, which is OK but not recommended.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Skip:

The first code is what I would like to add to the second macro below. I am essentially pulling on one cell value and then performing auto fill for a total of nine more additional rows for that one column.

Below are the sets of codes I am working with...

Sub DateProCDateAudit()
'
' DateProCDateAudit Macro
' Macro recorded 6/19/2009 by drmingle
'

'
Range("F1860:G1860").Select
Selection.AutoFill Destination:=Range("F1860:G1869"), Type:=xlFillDefault
Range("F1860:G1869").Select
Range("D1860").Select
Selection.AutoFill Destination:=Range("D1860:D1869"), Type:=xlFillDefault
Range("D1860:D1869").Select
Range("C1859").Select
Selection.AutoFill Destination:=Range("C1859:C1869"), Type:=xlFillDefault
Range("C1859:C1869").Select
Range("A1860").Select
Selection.AutoFill Destination:=Range("A1860:A1869"), Type:=xlFillDefault
Range("A1860:A1869").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
End Sub

The below code works just fine. Thanks for your help. I changed things up a bit...


Sub CullVisualAudit()
'
' Attempt2 Macro
' Macro recorded 6/19/2009 by drmingle

Dim wsSource As Worksheet
Dim wsDest As Worksheet
Set wsSource = ActiveSheet
Set wsDest = Windows("Brigman-Hannah - Audits - 2009.xls").ActiveSheet

wsSource.Activate
Range("B75:B84").Select
Selection.Copy
wsDest.Activate
Range("E1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wsSource.Activate
Range("C75:C84").Select
Application.CutCopyMode = False
Selection.Copy
wsDest.Activate
Range("B1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.WindowState = xlMinimized
wsSource.Activate
Range("D75:I84").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
wsDest.Activate
Range("H1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wsSource.Activate
Range("B60").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
wsDest.Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

wsSource.Activate
Range("B70").Select
Selection.Copy
Application.WindowState = xlMinimized
wsDest.Activate
Range("D1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

wsSource.Activate
Range("B71").Select
Selection.Copy
wsDest.Activate
Range("F1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

wsSource.Activate
Range("B72").Select
Selection.Copy
wsDest.Activate
wsDest.Range("G1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Set wsDest = Nothing

End Sub
 
Upvote 0 Downvote
1. Please use TGML code tags when posting code. Thanks.

2. Is this resolved for you? I can see why Skip has not responded any further, since you persist in ignoring what he is saying.

Gerry
 
Upvote 0 Downvote
Gerry:

This is resolved. As noted in earlier post, my ignoring Skip was unintentional.

Thanks for the post.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
687
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
M
  • Locked
  • Question
Reference Pivot Tables by name 1
Replies
2
Views
633
mintjulep
M
Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
905
remeng
remeng
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
405
mintjulep
M

Part and Inventory Search

Sponsor

Back
Top