Problems pasting sheets with macros

[daveonion]

Hi i've created a macro which copys data from one sheet and pastes it into another, however the macro fails whenever it pastes. It never actually worked although it worked during recording.
I have now tried many different ways to copy and paste, the latest message i am getting is subscript out of range, i will include the code that i am using, i am desperate for help as i have been doing this for 4 days, believing it would take half a day,

Dim valtest
Dim icounter

Range("a1&quot.Select
valtest = ActiveCell.Value
icounter = 1
Do While valtest <> &quot;&quot;
Range(&quot;a&quot; & icounter).Select
valtest = ActiveCell.Value
Icounter = icounter + 1
Loop
Range(&quot;A1:AP&quot; & icounter).Select
Selection.Copy
Workbooks.Open Filename:=&quot;U:\macros\datatogo&quot;
Sheets(&quot;sheet2&quot.Select
Range(&quot;A1&quot.Select
ActiveSheet.Paste

I would be grateful for any help, also the amount of records i have been copying has been 700 max.

 

[xlbo]

This should work nicely for you:
Slight rejig of the order and a one liner to pick up all the data and paste it


Dim ctWB As Workbook
Workbooks.Open FileName:=&quot;U:\macros\datatogo&quot;
Set ctWB = ActiveWorkbook

ThisWorkbook.ActiveSheet.Range(&quot;a1:a&quot; & Range(&quot;A1&quot.End(xlDown).Row).Copy Destination:=ctWB.Sheets(&quot;Sheet2&quot.Range(&quot;A1&quot



Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[daveonion]

hi geoff, i used the code but it only pasted the needed items within column A, but i need the range to go to AP, when i entered Ap, see below,

Dim ctWB As Workbook
Workbooks.Open FileName:=&quot;U:\macros\datatogo&quot;
Set ctWB = ActiveWorkbook

ThisWorkbook.ActiveSheet.Range(&quot;a1:ap&quot; & Range(&quot;A1&quot.End(xlDown).Row).Copy Destination:=ctWB.Sheets(&quot;Sheet2&quot.Range(&quot;A1&quot

It returned an error message, system error &h80010108(-2147417848), i have no idea what the problem is, any more help would be appreciated
thanks
Dave

 

[xlbo]

Just tested it and it works fine - it may be word wrap but the Destination:= line should be on the same line as the copy....

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[daveonion]

thanks, but i'm still having problems, i'm starting to believe there is a problem with the source sheet, because the code is making my computer crash.

 

[xlbo]

Bizarre - feel free to send me the workbook and I'll have a look. Will need to be in xl97 format tho (just do a saveas if you are usuing a higher version)
Geoff.Barraclough@Punchpubs.co.uk

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[bryanbayfield]

Maybe a corrupt workbook?

Perhaps you might have more luck if you copied all of the sheets into a new workbook, pasted opver all of the code too then save the new workbook under teh name of teh old one.

This possible solution was brought to you from the &quot;Big Bumper Book of 'Try Restarting Your PC' IT Helpdesk Solutions&quot;

It does sound as if you have a memory exception ie it's not the actual coding itslef that's causing the problem, but a blip in the workbook.

 

[xlbo]

LOL - I've just sent a virtually identical reply to Dave's email


Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[bryanbayfield]