EDN Admin
Well-known member
<span style="font-size:5.5pt; color:black; font-family:Verdana I have a workbook (Workbook1) that runs through some steps using visual basic, which ends up opening another workbook Workbook2). Once Workbook 2 is opened, I need to copy a section
of data from it, into Workbook1 int a specific worksheet. I was able to do this by recording a macro, but the challange is, the name of Workbook 1, and Workbook 2, will vary by User. When I created the macro it uses the files as they are currently
named. The section of code is below.
<span style="font-size:5.5pt; color:black; font-family:Verdana Heres the steps needed:
<span style="font-size:5.5pt; color:black; font-family:Verdana Open Workbook 2, from Workbook 1<br/>
Highlight and Copy the data from a dynamic range in Workbook 2<br/>
to a Specified Worksheet and cell in Workbook 1<br/>
Close Workbook 2 and re-save Workbook 1.
<span style="font-size:5.5pt; color:black; font-family:Verdana I then have additonal macros that take things from there, the first step being a Data Refresh for a Pivot table included in Workbook 1.
<span style="font-size:5.5pt; color:black; font-family:Verdana Is there a way to do what I am wanting to do?
<span style="font-size:5.5pt; color:black; font-family:Verdana Coding from MAacro Steps:
<span style="font-size:5.5pt; color:black; font-family:Verdana Range("A10").Select<br/>
Range(Selection, Selection.End(xlToRight)).Select<br/>
Range(Selection, Selection.End(xlDown)).Select<br/>
Selection.Copy<br/>
Windows( _<br/>
"Lisa Michelle Renfroe Efficient Assortment Analysis (MSA Data 52 WE 2011-08-05).xls") _<br/>
.Activate<br/>
Sheets("G360Data").Select<br/>
Range("A2").Select<br/>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br/>
:=False, Transpose:=False<br/>
Sheets("WT Pivot Table").Select<br/>
Range("A10").Select<br/>
Application.CutCopyMode = False<br/>
ActiveSheet.PivotTables("PivotData").PivotCache.Refresh
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Arial Narrow
View the full article
of data from it, into Workbook1 int a specific worksheet. I was able to do this by recording a macro, but the challange is, the name of Workbook 1, and Workbook 2, will vary by User. When I created the macro it uses the files as they are currently
named. The section of code is below.
<span style="font-size:5.5pt; color:black; font-family:Verdana Heres the steps needed:
<span style="font-size:5.5pt; color:black; font-family:Verdana Open Workbook 2, from Workbook 1<br/>
Highlight and Copy the data from a dynamic range in Workbook 2<br/>
to a Specified Worksheet and cell in Workbook 1<br/>
Close Workbook 2 and re-save Workbook 1.
<span style="font-size:5.5pt; color:black; font-family:Verdana I then have additonal macros that take things from there, the first step being a Data Refresh for a Pivot table included in Workbook 1.
<span style="font-size:5.5pt; color:black; font-family:Verdana Is there a way to do what I am wanting to do?
<span style="font-size:5.5pt; color:black; font-family:Verdana Coding from MAacro Steps:
<span style="font-size:5.5pt; color:black; font-family:Verdana Range("A10").Select<br/>
Range(Selection, Selection.End(xlToRight)).Select<br/>
Range(Selection, Selection.End(xlDown)).Select<br/>
Selection.Copy<br/>
Windows( _<br/>
"Lisa Michelle Renfroe Efficient Assortment Analysis (MSA Data 52 WE 2011-08-05).xls") _<br/>
.Activate<br/>
Sheets("G360Data").Select<br/>
Range("A2").Select<br/>
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br/>
:=False, Transpose:=False<br/>
Sheets("WT Pivot Table").Select<br/>
Range("A10").Select<br/>
Application.CutCopyMode = False<br/>
ActiveSheet.PivotTables("PivotData").PivotCache.Refresh
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Arial Narrow
View the full article