Forum Home
Press F1
Thread ID: 111876	2010-08-15 08:23:00	Create a macro to add data progressively	grego (15938)	Press F1

Post ID	Timestamp	Content	User
1127916	2010-08-15 08:23:00	Hi I just discovered macros about 48 hrs ago:horrified Any way I am trying to create a trade journal where I can enter data into a specific sheet and then click a button to transfer the data to another sheet that records the info progressively down the page. I managed to do a simple one, however it just writes over the data I already have. In other words it just rewrites to the same row each time. Then I tried to work out that if i used "Relative References" I thought it might progressively work down the sheet recording my data. i think I'm on the right track but just don't have the knowledge to record all the steps to get it to work. Please help? Thank You Greg:thanks	grego (15938)
1127917	2010-08-15 19:39:00	So when you started to record, you copied the data, then clicked to open a new sheet then pasted? It should record all those steps.	pctek (84)
1127918	2010-08-15 23:04:00	Hi Yes I had already created the sheet I want it pasted into along with appropriate headings. I have set up the headings,in the same order on both sheets, So When I copy the data from one sheet and paste it into the other sheet it pastes along the row and the data lines up. What I want to be able to do is use a form (in say sheet "A") repeatedly where I fill in the data after each share trade, click a button which then updates "sheet B" on the next available row. Presently my macro just overwrites the same row on "sheet B" each time I reuse the (macro) form from "Sheet a" ps. I have already created a simple macro to clear the form in "Sheet A" after each use and know how to assign a macro to an object :thanks Greg	grego (15938)
1127919	2010-08-16 00:33:00	'set start values rowpointer = 0 colpointer= 1 cellval = "BLANK" 'get blank line Do Until celval = "" rowpointer = rowpointer + 1 cellval = Sheets("Data").Cells(rowpointer, colpointer).Value Loop ' YOUR CODE HERE This is a quick hack I use to loop down a sheet in one column looking for a blank cell. When it finds one and exits the loop, you can use the 'rowpointer' variable in your 'Insert Data' code.	fred_fish (15241)
1127920	2010-08-16 01:45:00	Hi Fred Fish Thanks for your help. below is the full code (yours and mine) I replaced your line "cellval = Sheets("data").Cells(rowpointer, colpointer).Value with cellval = Sheets("Sheet1").Cells(rowpointer, colpointer).Value "Sheet 1 being the sheet that I want the data loaded into Would that be right? or are there other reference changes in the code required as well? Also Would you mind taking a look at my part of the code, Because when I combine your code and mine it still doesn't work. So i am assuming i have done something incorrect when recording Thank you Greg ' Macro10 Macro ' ''set start values rowpointer = 0 colpointer = 1 cellval = "BLANK" 'get blank line Do Until celval = "" rowpointer = rowpointer + 1 cellval = Sheets("Sheet1").Cells(rowpointer, colpointer).Value Loop Range("B20:J20").Select Selection.Copy Sheets("Sheet1").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A4").Select ActiveCell.Select Sheets("Visual Joural Entries").Select Application.CutCopyMode = False ActiveCell.Select Sheets("Sheet1").Select End Sub	grego (15938)
1127921	2010-08-16 02:00:00	Hi Fred Fish Thanks for your help . below is the full code (yours and mine) I replaced your line "cellval = Sheets("data") . Cells(rowpointer, colpointer) . Value with cellval = Sheets("Sheet1") . Cells(rowpointer, colpointer) . Value "Sheet 1 being the sheet that I want the data loaded into Would that be right? or are there other reference changes in the code required as well? Also Would you mind taking a look at my part of the code, Because when I combine your code and mine it still doesn ' t work . So i am assuming i have done something incorrect when recording Thank you Greg ' Macro10 Macro ' ' set start values rowpointer = 0 colpointer = 1 cellval = "BLANK" Range("B20:J20") . Select Selection . Copy Sheets("Sheet1") . Select ' get blank line Do Until cellval = "" rowpointer = rowpointer + 1 cellval = Sheets("Sheet1") . Cells(rowpointer, colpointer) . Value Loop Cells(rowpointer, colpointer) . Select Selection . PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A4") . Select ActiveCell . Select Sheets("Visual Joural Entries") . Select Application . CutCopyMode = False ActiveCell . Select Sheets("Sheet1") . Select End Sub Try that . Fixed typo in cellval variable & added line to select blank target cell before pasting . NOTE: This code is from a XL2k macro, may be some differences in your version . Also if you open the VB editor you can step through the code with the F8 key, so if it is failing you can figure out where, and also watch what each line does . See also "Add watch" on the Debug Menu to watch variable values .	fred_fish (15241)
1127922	2010-08-16 02:13:00	Also if you open the VB editor you can step through the code with the F8 key, so if it is failing you can figure out where, and also watch what each line does. I hit the f8 key after loading it into the editor and the following line came up with a "syntax error"message 'get blank line Do Until *cell*val = "" Regards Greg	grego (15938)
1127923	2010-08-16 02:37:00	I hit the f8 key after loading it into the editor and the following line came up with a "syntax error"message 'get blank line Do Until *cell*val = "" Regards Greg :) Looks like your copy/paste from the forum picked up the bold tags around my typo correction. take the '*' out of the variable name.	fred_fish (15241)
1127924	2010-08-16 02:52:00	Thank you Fred Fish that fixed the problem and it now does what i needed Your help is greatly appreciated :thanks:thanks:thanks:clap	grego (15938)
1