"ThisWorkbook" VBA help

[Wamhoi]

Hi,

I'm trying to copy data from a Workbook to the Master Workbook (Containing Macro). I created the following formula that keeps breaking.

With Thisworkbook.Sheets("Data").Range("A1").Select
.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.PasteSpecial Paste:=xlValues
End With

Any Ideas?

 

[JackDanIce]

You could just use:

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlValues

Or easier to read:

Dim i as Long
i = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & i).Pastespecial Paste:=xlValues

Your code implies you're already in the active workbook and sheet you want to paste the values into so there doesn't appear to be any point using the WITH part of your code to define as This Workbook.

 

[RoryA]

With ThisWorkbook.Sheets("Data")
   .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
End With

 

[JackDanIce]

Ignore my reply, mis-read the question, apologies - can't seem to edit my answer either.

 

[Wamhoi]

With ThisWorkbook.Sheets("Data")
   .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
End With

No worries JackDanIce.

rorya, I attached the code you provided but recieved a "Run-time error '429': ActiveX component can't create object" at:

With ThisWorkbook.Sheets("Data")

Thanks!

 

[RoryA]

Does your code workbook have a sheet called Data in it? Assuming it does, do you have any ActiveX controls on the sheet?
Also, does the ThisWorkbook module appear in the workbook project with that name? (it sounds to me as though you might have a corrupt workbook)

 

[Wamhoi]

Hi, I did get it to work. I just copied the Macro into a fresh workbook then ran it and it worked great!

In the old workbook I did have Data but I'm not sure about the activex

Either way, thank you soo much for your help.