Hi all,

I'm after a bit of help:

I have a worksheet which is a quotation with the prices, inclusions etc
coming from different sheets in the workbook. I have a sort macro which
sends all the unwanted bits down to the bottom of the sheet.
I want to be able to copy from cell A1 down to the end of the useful list
which is marked by the word "end" but after sorting could be on any row.

I then want to open a word template and paste the data.

Any help would be appreciated.

Hi,

Have you tried sending the data you dont want to another sheet instead of
the bottom of the same sheet?

"Jim" wrote:

Hi all,

I'm after a bit of help:

I have a worksheet which is a quotation with the prices, inclusions etc
coming from different sheets in the workbook. I have a sort macro which
sends all the unwanted bits down to the bottom of the sheet.
I want to be able to copy from cell A1 down to the end of the useful list
which is marked by the word "end" but after sorting could be on any row.

I then want to open a word template and paste the data.

Any help would be appreciated.

Hey,

I may need to revise the quote which would mean some of the information
would then be required so i'd rather leave it on the same sheet

"oli merge" wrote:

Hi,

Have you tried sending the data you dont want to another sheet instead of
the bottom of the same sheet?

"Jim" wrote:

Hi all,

I'm after a bit of help:

I have a worksheet which is a quotation with the prices, inclusions etc
coming from different sheets in the workbook. I have a sort macro which
sends all the unwanted bits down to the bottom of the sheet.
I want to be able to copy from cell A1 down to the end of the useful list
which is marked by the word "end" but after sorting could be on any row.

I then want to open a word template and paste the data.

Any help would be appreciated.

I'm nearly there!!! Bit of trial and error and some help from previous posts
and i've nearly got it!

So far i have:

Cells.Find(What:="5000000", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.select
ActiveCell.Offset(-1, -2).select
Range(Selection, Cells(1)).select
Selection.Copy

Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number < 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open("P:\0 Estimating
Programs\Development\quote.dot")

wdApp.Visible = True
wdApp.Activate
Application.CutCopyMode = False
Range("a1").select

The problems:

1/. where i quote the path and file name for the word document can this be
written into a cell in the excel workbook so it can be changed without having
to go into the VB code?
2/ when the data is pasted into word the data is moved over to the right by
one tab. It doesn't do this when i manually paste the data so why does it
happen when i use this macro?

Cheers

jim

"Jim" wrote:

Hey,

I may need to revise the quote which would mean some of the information
would then be required so i'd rather leave it on the same sheet

"oli merge" wrote:

Hi,

Have you tried sending the data you dont want to another sheet instead of
the bottom of the same sheet?

"Jim" wrote:

Hi all,

I'm after a bit of help:

I have a worksheet which is a quotation with the prices, inclusions etc
coming from different sheets in the workbook. I have a sort macro which
sends all the unwanted bits down to the bottom of the sheet.
I want to be able to copy from cell A1 down to the end of the useful list
which is marked by the word "end" but after sorting could be on any row.

I then want to open a word template and paste the data.

Any help would be appreciated.

"Jim" wrote:

I'm nearly there!!! Bit of trial and error and some help from previous posts
and i've nearly got it!

So far i have:

Cells.Find(What:="5000000", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.select
ActiveCell.Offset(-1, -2).select
Range(Selection, Cells(1)).select
Selection.Copy

Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number < 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open("P:\0 Estimating
Programs\Development\quote.dot")

wdApp.Visible = True
wdApp.Activate
Application.CutCopyMode = False
Range("a1").select

I have then created an auotopen module in quote.dot with the following code

Sub autoOpen()
'
' autoOpen Macro
' Macro recorded 15/06/2007 by James Cowell
'
Selection.Paste
Selection.HomeKey Unit:=wdStory
End Sub

The problems:

1/. where i quote the path and file name for the word document can this be
written into a cell in the excel workbook so it can be changed without having
to go into the VB code?
2/ when the data is pasted into word the data is moved over to the right by
one tab. It doesn't do this when i manually paste the data so why does it
happen when i use this macro?

Cheers

jim

"Jim" wrote:

Hey,

I may need to revise the quote which would mean some of the information
would then be required so i'd rather leave it on the same sheet

"oli merge" wrote:

Hi,

Have you tried sending the data you dont want to another sheet instead of
the bottom of the same sheet?

"Jim" wrote:

Hi all,

I'm after a bit of help:

I have a worksheet which is a quotation with the prices, inclusions etc
coming from different sheets in the workbook. I have a sort macro which
sends all the unwanted bits down to the bottom of the sheet.
I want to be able to copy from cell A1 down to the end of the useful list
which is marked by the word "end" but after sorting could be on any row.

I then want to open a word template and paste the data.

Any help would be appreciated.