Any excel 2003/VBa expert? - MensTennisForums.com
 
LinkBack Thread Tools
post #1 of 5 (permalink) Old 07-01-2008, 08:20 PM Thread Starter
Registered User
 
Join Date: Jul 2002
Posts: 448
                     
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  
Sponsored Links
Advertisement
 
post #2 of 5 (permalink) Old 07-01-2008, 09:42 PM
-
 
Neely's Avatar
 
Join Date: Jan 2004
Age: 33
Posts: 24,630
                     
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  
post #3 of 5 (permalink) Old 07-03-2008, 07:02 PM Thread Starter
Registered User
 
Join Date: Jul 2002
Posts: 448
                     
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  
post #4 of 5 (permalink) Old 07-04-2008, 01:07 AM
-
 
Neely's Avatar
 
Join Date: Jan 2004
Age: 33
Posts: 24,630
                     
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  
post #5 of 5 (permalink) Old 07-06-2008, 02:46 PM Thread Starter
Registered User
 
Join Date: Jul 2002
Posts: 448
                     
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  
Reply

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the MensTennisForums.com forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in









Human Verification

In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.



Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page



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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome