I'm going from column to column... i don't see what the problem is...

Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Co lumns("a")


Here's the whole code

Sub Print_MTD2()
Dim WBNew As Workbook
Dim WSNew As Worksheet

'build new Workbook/worksheet to copy data into
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
Set WBNew = ActiveWorkbook

'copy columns from MTD to new sheet
Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Co lumns("a")

End Sub

on 4/14/2011, Matthew Dyer supposed :
I'm going from column to column... i don't see what the problem is...

Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Co lumns("a")


Here's the whole code

Sub Print_MTD2()
Dim WBNew As Workbook
Dim WSNew As Worksheet

'build new Workbook/worksheet to copy data into
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
Set WBNew = ActiveWorkbook

'copy columns from MTD to new sheet
Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Co lumns("a")

End Sub

You need to specify a source AND target ADDRESS. So...

replace Columns("g") with Range("$G:$G")
replace Columns("a") with Range("$A:$A")

OR
wrap them in Range() and specify ADDRESS...

Range(Columns("g").Address)
Range(Columns("a").Address)

OR
use Columns("g").EntireColumn for the copy
use Columns("a") for destination

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Actually, you can use 'Columns("a") for the destination in all my
examples and it works fine. The key point is that you give the Copy
method a range address; giving it a column label doesn't substitute for
that.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Apr 14, 2:41*pm, GS wrote:
on 4/14/2011, Matthew Dyer supposed :





I'm going from column to column... i don't see what the problem is...

Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Co lumns("a")

Here's the whole code

Sub Print_MTD2()
Dim WBNew As Workbook
Dim WSNew As Worksheet

'build new Workbook/worksheet to copy data into
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
Set WBNew = ActiveWorkbook

'copy columns from MTD to new sheet
Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Co lumns("a")

End Sub

You need to specify a source AND target ADDRESS. So...

* replace Columns("g") * with * Range("$G:$G")
* replace Columns("a") * with * Range("$A:$A")

OR
wrap them in Range() and specify ADDRESS...

* Range(Columns("g").Address)
* Range(Columns("a").Address)

OR
* use Columns("g").EntireColumn for the copy
* use Columns("a") for destination

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

I tried all three examples and i still keep getting the type mismatch
error...

On Apr 14, 2:20*pm, Matthew Dyer wrote:
I'm going from column to column... i don't see what the problem is...

**snip**
My two cents is that the problem isn't really with the way you've
called out the columns.
This is what works for me:

Dim strBookName As String
Dim strSheetName As String

Workbooks.Add (xlWBATWorksheet)

strBookName = ActiveWorkbook.Name
strSheetName = ActiveSheet.Name

Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("G").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("A")

Cheers,
ABS

It happens that Matthew Dyer formulated :
I tried all three examples and i still keep getting the type mismatch
error...

Matthew,
I tested all 3 examples with actual data across 2 workbooks and they
worked for me.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Apr 14, 3:36*pm, ABS wrote:
On Apr 14, 2:20*pm, Matthew Dyer wrote: I'm going from column to column... i don't see what the problem is...

**snip**
My two cents is that the problem isn't really with the way you've
called out the columns.
This is what works for me:

Dim strBookName As String
Dim strSheetName As String

Workbooks.Add (xlWBATWorksheet)

strBookName = ActiveWorkbook.Name
strSheetName = ActiveSheet.Name

Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("G").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("A")

Cheers,
ABS

This worked for me. Not sure why but it did...

Matthew Dyer presented the following explanation :
On Apr 14, 3:36*pm, ABS wrote:
On Apr 14, 2:20*pm, Matthew Dyer wrote: I'm
going from column to column... i don't see what the problem is...

**snip**
My two cents is that the problem isn't really with the way you've
called out the columns.
This is what works for me:

Dim strBookName As String
Dim strSheetName As String

Workbooks.Add (xlWBATWorksheet)

strBookName = ActiveWorkbook.Name
strSheetName = ActiveSheet.Name

Workbooks("Analytics.xlsm").Worksheets("MTD").Colu mns("G").Copy _
Destination:=Workbooks(strBookName).Worksheets(str SheetName).Columns("A")

Cheers,
ABS

This worked for me. Not sure why but it did...

Strangely, your original code also works now too!!!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc