• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Copy and Paste from one sheet to another sheet

I

Injinia

Member
I would like help to write a macro that copys data and pastes it on a new sheet always on the next empty row. I have this but it keeps on giving me an error @ " Set emptyCells = Range("A:A").SpecialCells(xlCellTypeBlanks)"


Sheets("accuracy").Select

Range("B23:L29").Select

Selection.Copy

Sheets("Sheet 1").Select

Set emptyCells = Range("A:A").SpecialCells(xlCellTypeBlanks)

Range(emptyCells.Cells(1).Address, emptyCells.Cells(1).Offset(7, 10).Address).Select

ActiveSheet.Paste

Range("C23").Select

Sheets("accuracy").Select
 
Hi ,


I copied this macro into the ThisWorkBook section of VBA Project , and I did not get any error when I ran it.


However , I do not know what this macro is supposed to do ; can you clarify what your requirement is ?


If you have an Option Explicit statement at the top , this statement can generate an error since emptyCells is not declared before it is used.


Narayan
 
Hi,


I am a novice when it comes to VBA, but I will try my best to explain. On the sheet accuracy, I have data which I want to be able to copy and paste on Sheet 1, every time i run the macro. The data on Sheet "accuracy is always in the same cells. I would like that the macro copies this data to the next sheet, in "continous fashion" ie, copy paste, run macro again and then to the next empty row and copy paste
 
Hi ,


OK. Understood. There is a defined data range on the sheet labelled "accuracy" ; whenever you run this macro , it should copy this defined data range , and paste it on the next empty row in the sheet labelled "Sheet 1". Is this correct ?


The defined data range is B23:L29.


The paste range always starts with column A and the first empty row in column A ; is this correct ?


Narayan
 
Keeping it short & sweet

[pre]
Code: 
Sub TransferData()
Dim LastRow As Integer

'Where is the last cell with data?
LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row

'Transfer data
Sheets("accuracy").Range("B23:L29").Copy Worksheets("Sheet 1").Cells(LastRow + 1, "A")
End Sub
[/pre]
 
Hello Mr. Luke Sir i am just little bit confuse on the above code


will you please explain in a sample book if possible


Hence i oblige


Thanking you
 
@sgmpatnaik


Sure thing. I'll go through line by line.

[pre]
Code: 
Dim LastRow As Integer
This tells VB that I'm going to use a variable called "LastRow", and that it will be an integer. Defining your variable not only helps save memory as VB doesn't have to try and figure out what type of variable you want each time, but it's also good practice in coding as it can help you make sure you didn't make a typo and lets future readers know what you are using things for.

[pre][code]LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row
[/pre]

Here we assign as value to LastRow. We want to find the last cell within column A of "Sheet 1" that has a value. So, we start at the object A65536 (on sheet 1), and use End(xlUp). This is equivalent to using the Ctrl+arrow key on your worksheet. So, VB starts at A65536, then goes up until it runs into something, aka, that last cell in column A with a value. We then take the row number for that cell and store it in our variable.


Sheets("accuracy").Range("B23:L29").Copy Worksheets("Sheet 1").Cells(LastRow + 1, "A")[/code][/pre]
The first half of this line is simply stating what range we want to copy. That latter half is the destination. The key here is that we used the Cells method instead of the Range method. We did this because the 2 arguments for Cells is Row# and Column #. Since the Row# is going to be a variable, this is more conducive to what we want. So, I'm telling the code to paste onto Sheet 1, in column A, in row (LastRow+1). Remember, LastRow is the row with last cell with data, so the "+ 1" tells the code to go into the next cell, so we don't paste over any old info.


Does that help?
 
Thank Q Very Much Sir


Which Doubt was confuse to my mind that one is clear and i am feel free from that question


and Thank Q very much to spend some time to explain the above code
 
Hi Luke.


I'm using your code you posted above - thank you for that.


I'm having some trouble with where the data is being pasted though. My data sheet has hidden rows due to filtering, and the paste destination is always +1 of the source row.


I need to figure out how to start pasting @ row 1, and so forth.


Any ideas? Thanks so much.
 
Hi, jobeac!


Looking at the copy statement:

Sheets("accuracy").Range("B23:L29").Copy Worksheets("Sheet 1").Cells(LastRow + 1, "A")

the target is first empty cell in column A of sheet 'Sheet 1'. If I didn't misread you, change:

LastRow + 1

by:

1

This should place pasted range from row 1 in advance.


Is that what you were asking for?


Regards!


PS: BTW, it'd be better and cleaner if next time start your own new topic and place links to other related posts if necessary, even more with so older topics.
 
Hi,


Thanks for all experts to the above info.


I just need small changes in the macro of Mr. Luke


The macro is coping one specified range. Please modify little bit and add some loop for me as per the following info.


I have one worksheet (sheet name: data) contains 100 columns.


I need to copy paste Column A+b+c+d into one worksheet, A+e+f+g to next worksheet, A+h+i+j to next worksheet and A+k+l+m to next worksheet and so on up to empty column (which is last column in that data range).


Pleas let me know if you have any questions in this.
 
You must log in or register to reply here.
Back
Top