Excel 2007 - Macro copy and paste between work sheets, onto a new line

2
votes

I am using Excel 2007, I am trying to create a macro between 2 different worksheets.

I am trying to copy cells and paste them into worksheet 2. This I realise is quite simple but here is my problem - I want that every time the macro is run, that it can copy the value onto a new row in worksheet 2.

For example:
Example of data: Row headings : (a1) Month, (b1) 1, (c1) 2, (d2) 3. Data :(a1) July-12, (b2) 2, (c2) 5, (d2) 1 Data : (a1) Aug-12, (b2) 1, (c2) 4, (d2) 2.

Location of where to be copied. Row headings : (a1) Month, (b1) values of 1, (c1) values of 2, (d2) values of 3.

I have never used VB before, I am used to just using the forumlaes on Excel.
I have tried the following: Range("A1:A2").Select Selection.Copy Range("C4").Select ActiveSheet.Paste

I am trying to automate my spreadsheet, rather than having to note a figure from one work sheet, then write it in another worksheet.

I am unsure if I am over complicating things in my own mind, or if this is more complex..

Many thanks in advance to all responses.

1
excelcopyexcel-2007vba
Welcome to stackoverflow. Have you already tried anything? People are more eager to help if you show some research effort before asking. Otherwise, you will appear as a help vampire – JMax
Hello, thank you. Yes I tried multiple different VBA macro codes, but was only able to copy and paste the cell itself, or search for where the last data was centered. Beyound this I am stuck on whether what I am trying to do is actually possible. For example I had different examples of macro codes like this: Range("A1:A2").Select Selection.Copy Range("C4").Select ActiveSheet.Paste I was able to use some guidance already online to help me with this (I havent used VB before) – Kiwi
I have no idea what you are trying to do after spending considerable reading your question. You say you are trying to copy one cell, then give an example of copying four values. You say you are just pasting onto a new row, but then give an example where this is not actually a new row and rather pasting onto an existing row. – enderland
@Kiwi: you can edit your question to make it clearer thanks to the link at the bottom left of your question. Show us the code you've tried, what worked and where you are stuck, so that we could help you more precisely – JMax
Apologises, I was finding it difficult to sum up exactly what I am trying to do. Hopefully now this is clearer - I tried to use the aid of images to help with my explaination but StackOverflow states I have not got a high reputation yet to do this. Many thanks – Kiwi

1 Answers

0
votes

I might be very late with this but here you can find the last row of Sheet2 by

Lrow=ThisWorkbook.Sheets("Sheet2").Cells(65536,).end(Xlup).Row

Once you have found the last row you can paste the range in the next row after thelast row by incrementing it by 1 as shown below.

Range("A1:A2").Select 
Selection.Copy Range("C" & Lrow+1).Select ActiveSheet.Paste

Hope this helps!