type mismatch error

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

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
 
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
Click to expand...

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
 
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.
 
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 -
Click to expand...

I tried all three examples and i still keep getting the type mismatch
error...
 
I'm going from column to column... i don't see what the problem is...
Click to expand...
**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
 
It happens that Matthew Dyer formulated :
I tried all three examples and i still keep getting the type mismatch
error...
Click to expand...

Matthew,
I tested all 3 examples with actual data across 2 workbooks and they
worked for me.
 
**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
Click to expand...

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