Macro to copy info from one column to another

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have a tough macro to figure out! There are 3 parts to it. Here is what I have:

I have an excel worksheet that is currently filtered by a project #. I have certain columns that I would like to copy the information from one column to another. For example, copy column B to J and column A to I. I need for the headers to stay the same at the top which consists of the first two rows.

Next, I have several columns that I need to cut the information from one column and paste to another column. However, if any of the cells in the column I am cutting are blank, then do nothing for those particular cells, else if there is information in them then cut and paste to another column. Also, need to keep headers in first two rows. Examples in my worksheet are cutting columns F and pasting to E, H to G, K to A, L to B, P to O, R to Q, V to U, X to W, Z to Y AB to AA and AD to AC.

Next, there are several columns that need to be blanked out as the last step in the macro (excluding headers). These columns are M, N, S and T.

Please keep in mind that I only want the macro to run on the cells that we see shown on the screen as I have it filtered. Everything else that is not filtered should not be affected.

Can anyone help?? Please let me know if you need further details. Thanks so much!!!!!



3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
quote
I have an excel worksheet that is currently filtered by a project #. I have certain columns that I would like to copy the information from one column to another. For example, copy column B to J and column A to I. I need for the headers to stay the same at the top which consists of the first two rows.
unquote

FIRST COPY THE FILTERED DATA IN ANOTHER SHEET OF THE FILE SO THAT IT CAN BE RETRIEVED IF THERE IS A MESS UP

try this macro and check whether it does what you want.

Sub copying()
Dim r As Range, r1 As Range, r2 As Range
Dim j As Long, c As Range
Set r = ActiveSheet.UsedRange
'MsgBox r.Address
Set r1 = Range(r.Cells(2, 1), r.End(xlDown).End(xlToRight))
'MsgBox r1.Address
Set r2 = r1.Columns("a:B").Cells.SpecialCells(xlCellTypeVisible)
'MsgBox r2.Address
For Each c In r2
If c.Rows.Hidden = False Then
c.Copy Cells(c.Row, c.Column + 8)
End If
Next c
MsgBox "macro copying is over"
End Sub


about other problems in your postings I shall look into them.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
your second problem

quote
if any of the cells in the column I am cutting are blank
unquote

what is meant by cells blank
1.do you mean all the filtered cells in the column blank. in that case the whole filtered column to be left as it is with blank cells
or
2.any one or more (not all)of the filtered cells blanks and those blanks will be left blank and other cells to be cut and pasted.

reply quoting my message.

regarding the same second problem
quote
cutting columns F and pasting to E, H to G, K to A, L to B, P to O, R to Q, V to U, X to W, Z to Y AB to AA and AD to AC.
unquote

what is the logic of cutting F and pasting to E(previous column) but not true because K is to be cut and pasted to A

Is there any logic in these requirements.
Yes, #2 is meant by blank cells. In the filtered selection, I will have some cells that are blank and some that have information in them. When the column gets pasted into another column, I want to avoid the blank cells blanking out information that is already in the new column. Also, when I say cut and paste.....I want the information to be cut out but leave the column blank and not remove completely.

2.any one or more (not all)of the filtered cells blanks and those blanks will be left blank and other cells to be cut and pasted.

To answer the second part of your question - there are two columns that don't go in the same order as far as copying one column and pasting to the column right before it. K to A and L to B. If it would make the macro easier to have this changed so that it would be like all the others, I can do that. It's just the way I have it set up.

Thanks for your help!
I tried out the code you provided above for the first part and it worked! The only issue is that it copied the second row which is a header. How should I modify the code to exclude row 2 also(it's already excluding row 1)? Thank you!!
I figured out how to start it at row 3 and also figured out the very last part - blanking out certain columns. I'm still trying to figure out the middle piece, which is to cut certain columns and paste to others, except when the cell is blank, do nothing to those. Also, only want the macro to run on the filtered info.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
not clear what you want . In the absence of your database I am not able to understand what I want.
will it be possible for you to upload your file (at least a truncated version)-filtered sheet- in speedyshare.com and explain clearly what you want . In uploading in speedyshae delete the password.


alternately, if the administrator approves, send the file to me as attachment by email and once again explain what you want giving examples with reference to your data