473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

How to save the data in seq in the target sheet using Macros in Excel

Assume it i have a data sheet of the 15 column & i have created a command button.
The command button needs to be configured in such a way that,
1. whenever i update the 15 columns & i click the same, the same needs to be saved in one of the targeted sheet.
2. Then i clear the data sheet & re-enter the some other data, then i click the command button, the system should paste the data continuing to the previous saved data (step 1).

Plse advise whether it is possible to configure the data in such a way in the Macros
Aug 30 '10 #1
6 1492
Guido Geurs
767 Expert 512MB
This will copy the data from sheet(1) and append it on sheet(2)=

Expand|Select|Wrap|Line Numbers
  1. Sub CommandButton1_Click()
  2. Dim ARRAYDATA() As Variant
  3. '§ read data in array
  4.    ARRAYDATA = Range("A1").Resize(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)
  5. '§ copy Array to range
  6.    With Sheets(2)
  7.       If .Range("A1") = "" Then ' is it the first data
  8.          .Range("A1").Resize(UBound(ARRAYDATA), UBound(ARRAYDATA, 2)) = ARRAYDATA
  9.       Else
  10.          .Range("A" & .Range("A1").End(xlDown).Row + 1).Resize(UBound(ARRAYDATA), UBound(ARRAYDATA, 2)) = ARRAYDATA
  11.       End If
  12.    End With
  13. End Sub
Aug 31 '10 #2
Hi,

Thanks for the details which you had provided, but i tried the same which seems it doesnt work.

Let me again try to example you my requirement thru an example. Plse refer to the below attachment.

Step 1:-
In Sheet1, is used to feed the data & print the Invoice. On Sheet2 is where the data needs to be saved.
'Save & Print' command button is been shown in the Sheet1.

Step 2:-
When i updated the data in Sheet1 & click 'Save & Print' command button, system should save the data in sheet2 under the respective heading as the data in Sheet1.

Step 3:-
After i done the above steps, i will clear all the data in sheet1, but data copied in sheet2 should remain.

Then again i will feed the data in Sheet1 & then click 'Save & Print' command button, then data updated in Sheet1 should be pasted from the next row after the first data is been saved as per Step 1

Plse advise whether the above is possible thru Macro.

Thanks for your Help in advance
Aug 31 '10 #3
Guido Geurs
767 Expert 512MB
Is it possible to attach Your XLS in BYTES?
Also, if possible, attach an example of how the end result must be after clicking the command button.
From I understand for now, the data is not in rows and columns in Sheet(1) but somewhere on sheet(1).
If I have an example, I can write the macro to do just the things You want.
Because now I guessing what the code must be.
Aug 31 '10 #4
HI,

I tried to upload the attachment, but the system does not allow to upload the same, as it shows some error.

Is it possible to provide us your email address, so that i can forward the same to that, for your further analysis.

Thanks & Have a Nice Weekend.

Best Regards,
Harry
Sep 3 '10 #5
Hi,

Hi tried to reformat some changes in the attachment & i had uploaded the same in the Bytes. so, the system allowed to upload now.

Plse find the attachment. It contains two sheet.
Sheet 1:- It contains the forms wherein the details are going to be feeded
Sheet 2:- is the target sheet where the data needs to be saved.

When we update the details in Sheet1 as per the below sample & click the command button 'Save & Print', system should copy all the Blue Highlighted details in Sheet1 to Sheet2 as per the format.

Again the details will be cleared the details in Sheet1, then the user re-feed the details again & click the 'Save & print' button, then the re-updated details needs to be saved in the Sheet2 from the next column after the previously saved details.

Kindly check & advise whether the above would be configured in the system.

Thanks,

Best Regards,
Harry
Attached Files
File Type: zip Sample.zip (7.1 KB, 73 views)
Sep 14 '10 #6
Guido Geurs
767 Expert 512MB
solution: see attachment.
Sep 15 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: sifar | last post by:
Hi, I am right now learning PHP & want to know if there is a way to send web form data to an Excel sheet located on a network. My windows xp PC doesnot have a copy of Excel. Also i am not...
1
by: lldavid | last post by:
Hi, Can you kindly provide me sample VB codes to save a data into one single excel file with data in Sheet 1 and Sheet 2? All i was able to do is save data in Excel Sheet 1. Thanks for the...
1
by: Izzy | last post by:
Here is the code. Dim conExcel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;""" Dim cmdOLE As OleDb.OleDbCommand Dim conOLE...
0
by: sajithkahawatta | last post by:
is there any one used aspose.excel i used automation to read a exel doc .but we have to install ms excel to do that so i suppose to use aspose.excel can any one give me the 'Aspose.Excel.dll' and...
3
by: sajithkahawatta | last post by:
is there any one used aspose.excel i used automation to read a exel doc .but we have to install ms excel to do that so i suppose to use aspose.excel can any one give me the 'Aspose.Excel.dll' and...
1
by: gowthamkumar | last post by:
HII ... please give me a code to open a new excel sheet using VB so that i can also directly add some contents in the cells of the excel sheet in VB itself .. i ve use the existing excel sheet code...
5
by: tsanthoshk | last post by:
Hi All I am trying to read and modify input excel file. when i update the data in excel sheet already existing format is washing out. It mean if excel sheet contain colors those are not appearing...
0
by: Shikha1234 | last post by:
Requirement - Excel sheet should be populated with data from the database Issue - Excel sheet dialog box is opening with Open, save, Cancel. But, there is again a dialog box which says that the...
2
by: pulavarthipraveen | last post by:
Overview: We have a requirement in the c#.NET 1.0 windows application. There will be some input text file in the user’s machine. The user should browse and select the input text file and also select...
4
by: =?Utf-8?B?Sm9zaW4gSm9obg==?= | last post by:
I could create MS Excel sheet using ASP.NET 2.0 with C# but it is not being created in some systems, following error occurs when the program compiles : Microsoft Office Excel cannot open or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.