Any excel 2003/VBa expert? - MensTennisForums.com

MensTennisForums.com

MenstennisForums.com is the premier Men's Tennis forum on the internet. Registered Users do not see the above ads.Please Register - It's Free!

Reply

Old 07-01-2008, 08:20 PM   #1
julien
Registered User
 
Join Date: Jul 2002
Posts: 448
julien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond repute
Default Any excel 2003/VBa expert?

Hi,

I'm starting to make some macros on Excel at work, so this will be a very easy question: I would like to copy a cell (B6) that is on Sheet1 and copy it into Column A in Sheet2 and repeat this as long as there is data in colum E in Sheet2.
That means that if E31 is not empty in Sheet2, then copy Sheet1.B6. Same with E32, etc, and stop the copy at the first cell that is empty in columE.Sheet2.

I hope this is understandable. I apologize for not pasting the code I already set up, but I did this at work, and I'm home now.
julien is offline View My Blog!   Reply With Quote
Sponsored Links
Advertisement
 

Old 07-01-2008, 09:42 PM   #2
country flag Neely
-
 
Neely's Avatar
 
Join Date: Jan 2004
Age: 31
Posts: 24,630
Neely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond repute
Default Re: Any excel 2003/VBa expert?

Code:
Sub Macro1()
Dim zeile As Long
Dim i As Long
zeile = Cells(Rows.Count, 5).End(xlUp).Row
Sheets("Sheet2").Select
For i = 1 To zeile
If Sheets(2).Cells(i, 5).Value <> "" Then Sheets(2).Cells(i, 1).Value = Sheets(1).Cells(6, 2).Value
Next i
End Sub
This script checks if a row of the E column in Sheet2 is empty. If not, then it copies the value from Sheet1 and Cell B6 into the row of column A of Sheet 2.
__________________
Carlos --- C'MON Lleyton!!! --- Kiwi!!! --- Jürgen --- Juan

Best of luck to all Germans : Nicolas Kiefer | Rainer Schüttler | Lars | Florian | Tommy
Neely is offline View My Blog!   Reply With Quote
Old 07-03-2008, 07:02 PM   #3
julien
Registered User
 
Join Date: Jul 2002
Posts: 448
julien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond repute
Default Re: Any excel 2003/VBa expert?

Entschuldigung for the late reply. Thank you, it works fine when the two sheets are in the same workbook. But could you tell me how to adapt it if the sheets are in two differents workbooks? If you have some time of course...

I can't find the right syntax. :/ Could it be that one of the files is not saved anywhere, but just opened in Excel?
julien is offline View My Blog!   Reply With Quote
Old 07-04-2008, 01:07 AM   #4
country flag Neely
-
 
Neely's Avatar
 
Join Date: Jan 2004
Age: 31
Posts: 24,630
Neely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond reputeNeely has a reputation beyond repute
Default Re: Any excel 2003/VBa expert?

Making the same work with two different workbooks would be rather easily possible.

Yes, one of the files could be just opened, but the other one should be fixed, saved file, also because this would be the file with the macro in it. Otherwise you had to insert the macro manually each time, right?

In the above example, should sheet2 be the just opened file or how do you need it?
Try to give as many infos as possible.
__________________
Carlos --- C'MON Lleyton!!! --- Kiwi!!! --- Jürgen --- Juan

Best of luck to all Germans : Nicolas Kiefer | Rainer Schüttler | Lars | Florian | Tommy
Neely is offline View My Blog!   Reply With Quote
Old 07-06-2008, 02:46 PM   #5
julien
Registered User
 
Join Date: Jul 2002
Posts: 448
julien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond reputejulien has a reputation beyond repute
Default Re: Any excel 2003/VBa expert?

Hi (sorry for the late reply again).

In the example above, sheet2 would be the "saved" file. I'll try to give details.
I receive changes of prices several times everyday. They're in the same model every time (let's call it sheet 1), so I always have to copy the columns A to F to another chart I have on my hard drive (sheet2).

But, there are others infos I would like to copy after the columns A to F. In the G column (sheet2), I would like to put the data that is located in B6 (sheet1) and repeat it as long as there is data in A:F (sheet2).

Then, I would repeat the same operation for other data in other cells in sheet1.

So I don't really need sheet1 to be saved.
julien is offline View My Blog!   Reply With Quote
Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


Copyright (C) Verticalscope Inc
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
vBCredits v1.4 Copyright ©2007, PixelFX Studios