• 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 for copying pasting to a new column every time

S

Saraah

Member
Hi All,

I need help on writing a macro. Attached is the sample file.
I want every time the macro is clicked , it pastes current column data "Column L" to the empty column at its left.
For example: in this instance it will paste the Current numbers in June column " Column D".
And if I click it next month it will paste the current numbers in July and so on.

Can somebody write this macro for me ?
I tried to write, Macro 6 in the file but its not working. Please help.
 

Attachments

  • Sample.xlsm
    22.5 KB · Views: 13
Hi !​
Code: 
    With Sheet1.[L5]
        If .Offset(, -1).Value = "" Then .Resize(21).Copy .End(xlToLeft)(1, 2)
    End With
Do you like it ? So thanks to click on bottom right Like !
​
 
HI Marc,

I could not really get where and how this code should be applied. Can you please make changes in the sample file I attached and send it to me again.

It would be very helpful.

Saraah
 
Hi Marc

I tried to replace the code but its not working. It says there is some syntax errror.
Can you check and let me know.

Saraah
 
Sorry Marc..not sure what you mean.
Can you please edit the sample sheet i sent through and send back to me.
It will be of great help.

Saraah
 

As the code works like a breeze on my side,
as you wrote you got some error,
I just asked for the exact message and
you post here within this thread your code using code icon …
​
 
With Sheet1.[L5]
If .Offset(, -1).Value = "" Then .Resize(21).Copy .End(xlToLeft)(1, 2)
End With

I tried exact this.
 
Sorry Marc. I used exact this. Not sure what you mean here.
I am not an excel expert , need help to do something i am trying to do.
Can you please edit the file i sent with the code you think will work.
Its really urgent for me to close this.
Would be extremely great if you can send me the file back with the code thats working.

Your urgent assistance to this will be appreciated.


Thanks
Saraah
 
Hello Saraah
In post #10 Mr. Marc L introduces a very good demonstration.
Press Alt + F11 to log in VBE and then clear the old lines in macro 1 then put the new lines
Generally take the sample ready for you
 

Attachments

  • Sample.xlsm
    18.9 KB · Views: 4
Thanks Yasser.
The macro works here but when i try to copy it into my original file, it doesnot work.
The original file has multiple sheets. I made changes in macro by replacing sheet1 with the sheet name in original file.
But its giving me an error saying "object required".
Can you help ?

Saraah
 
That's the problem with bad attachment :
code may not work with different workbook !

As here it is not the name but the worksheet's codename
(see explanation in VBA inner help),
just replace it by Worksheets("name") …
​
 
Thanks Marc.
I have another problem, original file has formulas while copying. So i want to do a paste special.
Can you guide me how to do a paste special in this case ?

Saraah
 
Mod previous codeline :

If .Offset(, -1).Value = "" Then .End(xlToLeft)(1, 2).Resize(21).Value = .Resize(21).Value …

Do you like it ? So thanks to click on bottom right Like !
​
 
Thanks Marc. It helped.
I have another query. A similar case.
Now i want to paste a range to the right hand side where there is empty column.
Is there going to be a different code for this ?
 
Hi Marc,
Below is the code
Sub Macronew()
With Sheet2.[B3]
If .Offset(, -1).Value = "" Then .Resize(14).Copy .End(xlToRight)(1, 2)
End With
End Sub

Also I am attaching sample sheet. Please refer to sheet 2. I want to copy all in column B up to B40 to the next empty column at the right every month.
Please help.
 

Attachments

  • Sample - Copy.xlsm
    512.1 KB · Views: 6
As written in Tip frame, use CODE tags !

Your code does not work as it's not the same worksheet layout
neither same logic ‼

Just warming a couple of neurones :​
Code: 
Sub Macronew()
    With Sheet2
        .Range("B4", .Cells(.Rows.Count, 2).End(xlUp)).Copy .[A4].End(xlToRight)(1, 2)
    End With
End Sub
Or easier with a fixed source range :​
Code: 
Sub Macronew()
    Sheet2.[B4:B40].Copy Sheet2.[A4].End(xlToRight)(1, 2)
End Sub
You should Like it !
​
 
Marc I used this code but it says as invalid. Below is the code. Can you please check.

Sub Macronew()
With Sheet2.Range("B4", .Cells(Rows.Count, 2).End(xlUp)).Copy.[A4].End(xlToRight)(1, 2)

End With
End Sub
 

CODE tags ?‼ :mad: And you can use the Code icon …

You just made a typo …
Erase entire procedure, copy my code, paste and enjoy !
​
 
When I am trying to use this to the original file, I am facing one issue.

The sheet name is different in my original file. For example if I change the sheet name from sheet2 to saraah and change that in the code as well, the code doesnot work. And also I want to value paste the numbers.

Can you please advise.
 
You must log in or register to reply here.
Back
Top