Copy Range problem btw two Spreadsheets?????

[mbarnett]

I can't get the below code to work. I'm trying to copy data from one spreadsheet in Sheet1 called Book1 and paste the data into a range in another spreadsheet. But it keeps crasing. Can anyone help.... Please

ub GetMyRates()
'will copy all data in ML rate sheet
Dim LastRow As Long
Dim tday As Date

Application.ScreenUpdating = False
Sheet1.Activate

Cells(65536, 3).Select
Selection.End(xlUp).Select
LastRow = Selection.Row

Sheet1.Range(Cells(1, 5), Cells(LastRow, 9)).Copy

Windows("Daily" & Format(tday, "mmdd&quot & ".xls&quot.Activate

DailyInfo.Activate
Range("Daily&quot.ClearContents
DailyInfo.Cells(109, 1).PasteSpecial (xlValues)

Application.ScreenUpdating = True
End Sub

 

[RobBroekhuis]

What do you mean by "it keeps crashing"? An error message? I would prefer to use a workbooks(...).activate over a windows(...).activate method (especially when you're in screenupdating=false). You could try making that change and see if it helps. No other obvious problems that I can see (of course, we can't see any previous variable declarations or assignments that may affect things).


Rob

 

[Zathras]

Here is your code re-written to make it a little easier to see and control what is going on. It still has some "magic numbers" for row and column referencing, but I wanted to keep it as close to what you currently have to make it possible for you to be able to follow it.

If you take the trouble to read it carefully and understand what is happening, you should be able to learn a bit about VBA coding. (Note that .Select is not used and therefore it is not necessary to turn screen updating on/off.)
[blue]

Sub GetMyRates()

[green]

'will copy all data in ML rate sheet

[/color]

Dim LastRow As Long
Dim DailyInfo As Workbook
Dim DailyInfoSheet As Worksheet

[green]

  ' Find last row to be copied

[/color]

  ThisWorkbook.Activate
  LastRow = ActiveSheet.Cells(65536, 3).End(xlUp).Row

[green]

  ' Set objects for target sheet

[/color]

  Set DailyInfo = Workbooks("Daily" & Format(Now, "mmdd") & ".xls")
  Set DailyInfoSheet = DailyInfo.Worksheets(1)

[green]

  ' Clear target area

[/color]

  DailyInfoSheet.Range("Daily").ClearContents

[green]

  ' Copy/PasteSpecial from columns "E" thru "I" to the target sheet

[/color]

  ActiveSheet.Range(Cells(1, 5), Cells(LastRow, 9)).Copy
  DailyInfoSheet.Cells(109, 1).PasteSpecial (xlValues)
  Application.CutCopyMode = False
End Sub

[/color]

 

[mbarnett]

Hi Zathras,

Thanks for your help. I'm slowly learning VBA. I copied the sub, but it crashes when it gets to Set DailyInfoSheet = DailyInfo.Worksheets(1). The error message is variable not defined. Any suggestions??

 

[Zathras]

Did you copy and paste my code exactly as posted or did you modify it somewhat?

I can reproduce the error but only by trashing the line that says
[blue]

  Dim DailyInfoSheet As Worksheet

[/color]
Or is there something special about the structure of your Daylymmdd.xls workbooks you haven't mentioned?

I just tried it again and it works fine for me, but then my Daily1029.xls only has the usual default 3 worksheets in it.

 

[mbarnett]

I got it to work...Thanks for your help