473,322 Members | 1,409 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,322 software developers and data experts.

Excel Row copy through VBA

Hi,

I am seeking a help from all excel VBA expert.

I have attached the excel file for reference.

In the "Input" sheet there is production schedule that will be generated on submit button click. And the same schedule will also have to COPIED in "Output" sheet. But I am not able to paste it in desired location (blue highlighted area).

Another challenge for me to retain relevant data corresponding to year and clear the rest.
That means suppose its a schdule for 34 Years, now if I again generate it with 20 years it will keep the values of class I and class II till 20 years only and clear the rest.

The same has to be in Output sheet also.

Some one please help at earliest.

Thanks and regards,

Santosh

The code within Submit_Click:

Expand|Select|Wrap|Line Numbers
  1. Sub Submit_Click()
  2. Dim no_years As Integer
  3. Dim i_val As Integer
  4. Range("c10").Activate
  5.  
  6. ActiveCell.EntireRow.Clear
  7.  
  8. ActiveCell = Range("b3").Value
  9. no_years = Range("b4").Value
  10. i_val = Range("b5").Value
  11.  
  12.  
  13. Do While no_years > 0
  14. ActiveCell.Offset(0, 1).Activate
  15. ActiveCell.Value = DateAdd("YYYY", i_val, ActiveCell.Offset(0, -1).Value)
  16.  
  17. no_years = no_years - 1
  18.  
  19. Loop
  20.  
  21. ' copy production schedule calender to all sheets
  22. ActiveCell.EntireRow.Copy Destination:=Sheets("Output").Range("A" & Rows.Count).End(xlUp).Offset(1)
  23.  
  24. MsgBox ("Production schedule calendar generated")
  25. End Sub
Attached Files
File Type: xlsx VBA_Sample.xlsx (18.9 KB, 373 views)
Jun 4 '15 #1
3 3263
zmbd
5,501 Expert Mod 4TB
skg0009,
+ For various reasons, most of us will not open unrequested attachments.

+ One question per thread.

Line22 is too much.
Say I have a table Range Sheet1!A1:C4 that I want to copy to sheet2 starting in cell Z5... then
Expand|Select|Wrap|Line Numbers
  1.     Worksheets("sheet1").Range("A1").Select
  2.     Selection.CurrentRegion.Select
  3.     Selection.Copy Destination:=Worksheets("Sheet2").Range("Z5")
  4.     Range("A1").Select
There are a ton of other methods... this is the more straight forward of them and works for a contiguous region of data. You can of course select the data range using various methods.

the above will answer part of your second question too with a bit of thought and novel application.
Jun 24 '15 #2
Thanks a lot.
Can you give me an idea, how do I clear irrelevant data, for ex, after copy and paste new data place in sheet 2 from cell Z5 to AI5, but already few data exist in sheet 2 till AI6.

How do I clear this extra cell data ?

Regards,
Jun 24 '15 #3
zmbd
5,501 Expert Mod 4TB
new question new thread please
Jun 24 '15 #4

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

Similar topics

9
by: Nathan | last post by:
A couple of months ago, I went looking for a .NET grid component with what I thought was pretty simple criteria - I wanted an interface that provided my users with Excel-like data entry capability....
0
by: Kay | last post by:
Hi all, I want to copy an entire row from a worksheet to another worksheet, when I set the excel app = visible and step thru the code I can actally see a row is appended to another worksheet,...
0
by: keithsimpson3973 | last post by:
I have the following code to populate an excel spreadsheet from vb6 and access database. It works great except that when it reads from the recordset and populates the spreadsheet, I would like it to...
0
by: six888 | last post by:
i need to make a report using excel. im given a few data in excel document for a 1 month report (1 document for each week). each data document have a few work sheet.i need only certain information in...
2
by: OfficeDummy | last post by:
Hi, everyone! Like I mentioned in the thread title, I need to copy&paste data between different workbooks, and it works fine. However, when the data has been copied to the destination workbook,...
0
by: Taxman | last post by:
Windows XP, MS Office Excel 2003 If the tasks, I’m trying accomplish have been addressed previously (separately or in combination). Please, provide the links or keyword search to find them. I’ve...
4
by: Gustavo Cesar | last post by:
Friends, I need to build a loop on VBA Excel to do the following routine: 1o Action: Select entire row #1 and copy 2o Action: Select entire row #2 and Paste Special -> Formulas 3o Action:...
5
by: sandy armstrong | last post by:
Hello everyone I have a question??? can excel copy data from mulitiple worksheets then paste it into a summary workbook then every time the orginal file is change or anything is added it also...
7
by: sandy armstrong | last post by:
Hello and goodMorning, Can anyone please help me with this. I have a database in excel that has 2 sheets full of data. I would like if i press a button to update then macro searches in Column O if...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.