dynamically selecting workbook and worksheet

Question

I have to copy and paste specific columns from one worksheet in workbook1 to another worksheet in the same workbook1 or it may be workbook2 also. I mean I want to dynamically select the source workbook and worksheet and also the destination workbook and worksheet. I must be able to select the columns that I want to copy also dynamically.

I've tried this:

Dim thisWb As Workbook
Dim destWb As String
Dim destSheet As Worksheet, FromSheet As Worksheet
Dim FromBook As String


Set thisWb = ThisWorkbook
Set destSheet = thisWb.ActiveSheet

FromBook = Application.GetOpenFilename
If FromBook = "False" Then Exit Sub

destWb = Application.GetOpenFilename
Workbooks.Open Filename:=destWb
Set FromSheet = destWb.Worksheets("Sheet1")

Set sourcecolumn = Workbooks("FromBook").Worksheets("sheet1").Columns("A")
Set targetcolumn = Workbooks("destWb").Worksheets("sheet2").Columns("B")
sourcecolumn.Copy Destination:=targetcolumn

There is an "invalid specifier" compile time error and destwb is highlighted on this line: Set FromSheet = destwb.Worksheets("Sheet1")

i have tried doing this with static workbooks,worksheets,column names and it works.

    Dim sourcecolumn As Range, targetcolumn As Range
    Set sourcecolumn = Workbooks("Book1.xlsm").Worksheets("sheet1").Columns("A")
    Set targetcolumn = Workbooks("Book1.xlsm").Worksheets("sheet2").Columns("B")
    sourcecolumn.Copy Destination:=targetcolumn

The problem is i want to select the workbooks,worksheets and columns dynamically...

Please show what you've tried. Without seeing your code it's hard to tell what exactly you are trying to achieve. — Jean-François Corbett
thanks for the reply....let me be clear...i want to copy columns from a worksheet in a workbook to some worksheet in someother workbook .so i want to select the workbooks,worksheets and the columns which i want to copy and paste dynamically...any help will b appreciated — user3172566
Ok, so you've tried doing this. Is there any specific problem? Don't force us to guess. — Jean-François Corbett
when i run the macro there is a compile time error and destwb is highlighted in "Set FromSheet=destwb.Worksheets("Sheet1")....it says invalid specifier — user3172566

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2014-01-09T08:06:17

There is some type confusion in your variable declarations. On the line on which you get the error, you're trying to assign a Worbook object reference to a String variable. This doesn't work because those are two different data types.

Here's a fix. I commented the lines that I changed:

Dim pthFromBook As String
Dim pthDestWb As String ' to store the path of the workbook file
Dim thisWb As Workbook
Dim destWb As Workbook ' to store reference to workbook object
Dim destSheet As Worksheet, FromSheet As Worksheet

Set thisWb = ThisWorkbook
Set destSheet = thisWb.ActiveSheet

pthFromBook = Application.GetOpenFilename
If pthFromBook = "False" Then Exit Sub

pthDestWb = Application.GetOpenFilename ' first get the path
Set destWb = Workbooks.Open(pthDestWb) ' then open the workbook
Set FromSheet = destWb.Worksheets("Sheet1")

Finally, I don't know if it's a typo, but on the last line above, there appears to be some confusion between From and Dest sheets/workbooks...

dynamically selecting workbook and worksheet

1 Answers