VBA Excel 2010 Copying/Pasting Between Workbooks/sheets, prompting paste locations

Question

I have a base spreadsheet that needs to gather data from many other spreadsheets (trials). Am trying to write a macro that accomplishes the following:

1) Prompt open file (different file used for each macro instance)

2) Copy column 1 from trial to a specific column in sheet 1 on base spreadsheet

3) Copy column 2 from trial to a specific column in sheet 2 on base spreadsheet, and so on..

4) While for each file the column that is being copied to is different (ie, trial 1 might be copied to Y2:Y102, trial 3 might be A2:A102), it will be the same range in each sheet

So what I want from the macro is for it to prompt me once for the range for one sheet, then continue to copy the columns into the same range but in different sheets without prompting.

Here is what I have so far:

Sub skymacroT1()

TrialFile = Application.GetOpenFilename
Workbooks.Open TrialFile
Range("B4:B104").Select
Selection.Copy
'
Windows("Sky Braced Pressure Combined.xlsm").Activate
Sheets("Lt Heel").Select
Dim MyRange As Range
Set MyRange = Application.InputBox("select cell", Type:=8)
MyRange.Select
Range("MyRange").Paste
Application.CutCopyMode = False
'
Windows("TrialFile").Activate
Range("C4:C104").Select
Selection.Copy
'
Windows("Sky Braced Pressure Combined.xlsm").Activate
Sheets("Lt Met").Select
MyRange.Select
Range("MyRange").PasteSpecial
Application.CutCopyMode = False

End Sub

However the code fails in two spots: Range("MyRange").Paste fails to paste and Windows("TrialFile").Activate doesn't open the window for the file I prompted for.

Any help would be greatly appreciated!!

You should avoid Copy Paste when possible, the clip board can cause many unexpected errors, and will also eat up resources in your computer, try settings the cells value to the value of the cells you are trying to copy — user2140261

L42 L42 · Accepted Answer · 2014-01-24T05:57:46

This Range("MyRange").Paste fails because Myrange is not a Named Range but rather a variable object of the Range type. It should be like this:

Myrange.Paste

This Windows("TrialFile").Activate doesn't work because TrialFile contains the full path of the Workbook. You only need the Workbook name to activate it using Windows. So it should be like this:

Workbooks.Open TrialFile
TrialFilename = Activeworkbook.Name
'~~> other code goes here
Windows(TrialFilename).Activate

I've also re-written your code below:

Option Explicit '~~> Force Variable Declaration
Sub skymacroT1()

'~~ Declare all variables
Dim TrialFile, wbTrial as Workbook, wbSky as Workbook
Dim MyRange as Range, MyRangeAdd as String 

TrialFile = Application.GetOpenFilename
Set wbTrial = Workbooks.Open(TrialFile)
Set wbSky = Workbooks("Sky Braced Pressure Combined")

'~~> Can't be ommitted since you need to see the first address in the destination sheet to copy to.
wbSky.Sheets("Lt Heel").Activate

'~~> Identify the objects first before you work on them
Set MyRange = Application.InputBox("select cell", Type:=8)
MyRangeAdd = MyRange.Address '~~> set the universal address for all sheets

'~~ Proceed with copying
wbTrial.Sheets(1).Range("B4:B104").Copy wbSky.Sheets("Lt Heel").Range(MyRangeAdd) 
wbTrial.Sheets(1).Range("C4:C104").Copy wbSky.Sheets("Lt Met").Range(MyRangeAdd)

End Sub

The above code is just modified based on how you want to structure your procedure.
I'd like to eliminate the Activate method all in all but i think you will need to let the user see the target range first so it can't be helped.

VBA Excel 2010 Copying/Pasting Between Workbooks/sheets, prompting paste locations

2 Answers