# Thread: If, Then, Copy (2002)

1. ## If, Then, Copy (2002)

I'll try and explain the best way I can, please see attached.
The sheet is auto populated using formula's to pick up data from a basic input sheet. The layout is in blocks of four's, each 4 to be copied and pasted to a seperate application. There are 15 blocks of four totalling 60 individual transactions. The other application will not accept blanks. In the event of there being (say) only 7 transactions, then I want the first copy button to select the first four, and the second copy button to select only the next three. So I am thinking along the lines of an If statement for each copy set? Any idea's?

2. ## Re: If, Then, Copy (2002)

I don't see any "copy buttons".

The "blocks" in your worksheet contain many blank rows and columns between the data. How are those to be handled?

3. ## Re: If, Then, Copy (2002)

The buttons are not in the attachment, but are in the actual file,on top of the squares in column K. As an example, based on the first block:

Complete block of 4 - B5:I14
Partial block of 3 - B5:I11
Partial block of 2 - B5:I8
Single transaction - B5:I5

The underscores in the lines between transactions are only required as part of the copy, where a transaction follows. For example if a single transaction is imput to the other application as B5:I7, the application would fail. Hope this makes sense!!

PS. Column I in the attachment is blank, but can contain data.

<hr>The "blocks" in your worksheet contain many blank rows and columns between the data. How are those to be handled?
<hr>
The layout is designed to fit the template in the other application.

4. ## Re: If, Then, Copy (2002)

The data are in rows 5, 8, 11, 14, 16, 19, 22, 25, 27, ... As you see, the progression isn't regular.
It would be easier if you inserted an extra row between the "blocks of 4", so that the data are in rows 5, 8, 11, 14, 17, 20, 23, 26, 29, ...
Would that be possible?

5. ## Re: If, Then, Copy (2002)

Lord only knows why I posted "that" old version, where did I get that from???? Yes, certainly!! as per attached.

6. ## Re: If, Then, Copy (2002)

Try something like this:

Sub CopyBlock(n As Integer)
Dim intStart As Integer
Dim intStop As Integer
Dim i As Integer
intStart = 12 * n - 1
If Cells(intStart, 2) = "" Then
MsgBox "No data", vbExclamation
Else
intStop = intStart
For i = 1 To 3
If Cells(intStart + 3 * i, 2) = "" Then
Exit For
Else
intStop = intStop + 3
End If
Next i
Range(Cells(intStart, 2), Cells(intStop, 9)).Copy
End If
End Sub

Sub Copy1()
CopyBlock 1
End Sub

Sub Copy2()
CopyBlock 2
End Sub

...

The macros Copy1, Copy2, etc. (to be completed up to and including Copy15) are the ones to call from the command buttons.

7. ## Re: If, Then, Copy (2002)

Hans, the code works perfectly, Thankyou!!
Now I appreciate that you cannot see my 'other application', so I am not expecting miracles here. The cursor within the other app would be positioned correctly to start, following the paste action, 2 enters would be required, which would again leave the cursor correctly positioned for the next copy (from excel) and paste to other app.
My question is, can I reduce 15 copies (macro's) and 15 manual paste's, to one macro where excel could auto fill my other app? In theory??

8. ## Re: If, Then, Copy (2002)

I have no idea whether this will work. I tried it with Word and it failed miserably.

Sub CopyBlocks()
Const strApp = "My application"
Dim n As Integer
Dim intStart As Integer
Dim intStop As Integer
Dim i As Integer
For n = 1 To 15
intStart = 12 * n - 1
If Cells(intStart, 2) = "" Then
Exit For
Else
intStop = intStart
For i = 1 To 3
If Cells(intStart + 3 * i, 2) = "" Then
Exit For
Else
intStop = intStop + 3
End If
Next i
Range(Cells(intStart, 2), Cells(intStop, 9)).Copy
AppActivate strApp
SendKeys "^V~~", True
End If
Next n
End Sub

Change the value of the constant strApp to the caption of the application window into which you want to paste.

9. ## Re: If, Then, Copy (2002)

When testing, I used

Const strApp = "Microsoft Word"

but

Const strApp = "Word"

or

Const strApp = "Document1"

(where Document1 is the name of the active document within Word) would have worked too. The code did activate Word and inserted paragraph marks, but failed to paste the Excel cells.

10. ## Re: If, Then, Copy (2002)

Thankyou Hans, I'll give this a go at work over the next few days and let you know how I get on. When you say that this failed with Word, I presume that you were using Const strApp = "Word"??

I am presuming that: ^V~~ is equivalent to 'Control V' to paste, enter, enter.? I know that 'Control V' to paste is not accepted in the app, but I should be able to find the correct command. I have a few preset macro's for the app already, so should be able to use the code to work out the commands, the tricky bit will be the activation of the app.

11. ## Re: If, Then, Copy (2002)

> I am presuming that: ^V~~ is equivalent to 'Control V' to paste, enter, enter.?

That's correct. Click in SendKeys and press F1 to get help for this instruction. If you scroll down the help subject, you'll see a list of keycodes that you can use.

12. ## Re: If, Then, Copy (2002)

<hr>Change the value of the constant strApp to the caption of the application window into which you want to paste.
<hr>
Hans, I have attached a snippet of code from a current macro that I use within my other app. From the code, am I right to think that 'constant strApp' would be "autECLSession"? **This is a macro that works direct with the app**

I still need to ascertain the paste command into the app, I did'nt get time today! Though my overall requirement is:

App: - F2
XLS: - Copy
App: - Paste, Enter, Enter - (Repeating these 2 lines through the loop)
App: - F4
App: - F9
End Sub

Is there anything here that would alter the macro that you kindly provided yesterday?

Many Thanks

13. ## Re: If, Then, Copy (2002)

autECLSession is the name of an Automation object, I assume it is not the caption of the window of the application - you need the latter for AppActivate.

Apparently the application supports VBScript. Does it come with documentation on how to automate it, or does the developer provide support?

14. ## Re: If, Then, Copy (2002)

Hans,
< not the caption of the window of the application - how would I find this?

I Know even less about VB than I know of VBA. No and No. I could ask our <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15> ever so helpful helpdesk (that until 3 weeks ago were based upstairs, they are now 600 miles away). But then I could also win the lottery tonight. end <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

15. ## Re: If, Then, Copy (2002)

Does the application appear in the Windows taskbar or is it hidden? The caption would be the title of the taskbar button.

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> The helpdesk is only 600 miles away? That's not very far - all too often nowadays it's continents away... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•