type mismatch error

[Matthew Dyer]

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

Workbooks("Analytics.xlsm").Worksheets("MTD").Columns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Columns("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").Columns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Columns("a")

End Sub

 

[GS]

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

Workbooks("Analytics.xlsm").Worksheets("MTD").Columns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Columns("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").Columns("g").Copy _
Destination:=Workbooks(WBNew).Worksheets(WSNew).Columns("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

 

[GS]

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.

 

[Matthew Dyer]

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...

 

[ABS]

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").Columns("G").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("A")

Cheers,
ABS

 

[GS]

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.

 

[Matthew Dyer]

**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").Columns("G").Copy _
Destination:=Workbooks(strBookName).Worksheets(strSheetName).Columns("A")

Cheers,
ABS

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

 

[GS]

Matthew Dyer presented the following explanation :

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

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