Need macro for copying values from single column and pasting in multiple columns

moramramesh

New Member
Joined
May 22, 2019
Messages
17
Hi All,

I need macro for the below task.
I need to copy each set of data under each header from column B to column D, column E, and so on.

Column AColumn B
Server1
Header1Header2
Date1Value1
Date2Value2
Date3Value3
Date4Value4
Server2
Header1Header2
Date1Value1
Date2Value2
Date3Value3
Date4Value4
Server3
Header1Header2
Server4
Header1Header2
Date1Value1
Date2Value2
Date3Value3
Date4Value4

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Target state (I want the above values from col B to be pasted to col D, col E etc in horizontal direction to plot the graph):
Column DColumn EColumn FColumn G
Header2Header2Header2Header2
Value1Value1Value1
Value2Value2Value2
Value3Value3Value3
Value4Value4Value4

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>



Note: There are empty rows between server names and headers. Also there are empty rows between values and server names
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this
Assuming that your data starts in A1

Code:
Sub copying_values()
    Dim a As Range, c As Long
    c = 4
    For Each a In Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
        Cells(1, c).Resize(a.Count, 1).Value = a.Value
        c = c + 1
    Next
End Sub
 

moramramesh

New Member
Joined
May 22, 2019
Messages
17
Thank you very much Dante Amor, it is working perfectly fine. But I forgot to mention one thing. I need the server names (server 1, server 2 etc) that are present in column A to be copied to the target state columns as first values in each target column instead of the Header names (Header1, Header2 etc) like below:
Target State:
Server1Server2Server3Server4
Value1Value1Value1
Value2Value2Value2
Value3Value3Value3
Value4Value4Value4

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Thank you very much Dante Amor, it is working perfectly fine. But I forgot to mention one thing. I need the server names (server 1, server 2 etc) that are present in column A to be copied to the target state columns as first values in each target column instead of the Header names (Header1, Header2 etc) like below:
Target State:
Server1Server2Server3Server4
Value1Value1Value1
Value2Value2Value2
Value3Value3Value3
Value4Value4Value4

<tbody>
</tbody>

Try this

Code:
Sub copying_values()
    Dim a As Range, c As Long
    c = 4
    For Each a In Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
        Cells(1, c).Resize(a.Count, 1).Value = a.Value
        Cells(1, c).Value = a.Cells(1, 1).Offset(-1, -1).Value
        c = c + 1
    Next
End Sub
 

moramramesh

New Member
Joined
May 22, 2019
Messages
17

ADVERTISEMENT

Server names (server 1, server 2 ... etc) are not getting copied.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I need to know exactly in which row and in what column you have the information.
Upload an image or describe the position of the data.
 

moramramesh

New Member
Joined
May 22, 2019
Messages
17

ADVERTISEMENT

I'm unable to upload the image. Data starts from first cell (Server1)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
What do you have in A2, empty cell? or value?
What do you have in A3?
What do you have in B1, what do you have in B2?
etc...

Put your data as shown in A and B, the result will be in D, E, F and G

Excel Workbook
ABCDEFG
1Server1Server1Server2Server3Server4
2Header1Header2Value1Value15Value21
3Date1Value1Value2Value16Value22
4Date2Value2Value3Value17Value23
5Date3Value3Value4Value18Value24
6Date4Value4
7Server2
8Header1Header2
9Date1Value15
10Date2Value16
11Date3Value17
12Date4Value18
13Server3
14Header1Header2
15Server4
16Header1Header2
17Date1Value21
18Date2Value22
19Date3Value23
20Date4Value24
Hoja3
 

moramramesh

New Member
Joined
May 22, 2019
Messages
17
Hi Dante,

Here is my exact requirement:
Step1:
My Source data is in Columns A & Columns B. Now the data in Cols A & B has to be moved into the target columns C to columns F as shown in the below image.
Note: From the datetimestamp, only hours:minutes should be copied into the target cells and column header should be named as Time. Time should be copied only once as it is same for all the servers.

Step2: If any servers doesn't have data, then those columns should get deleted. In this case Server2 i.e. column E should be removed.

Step3: After the end of last column, new column should be added with header name as "SLA" and values as 50 in all the cells under this column

Step4: Line Graph should be plotted for the data present in the columns C to G. i.e. C1 to G3 cells
AB C DEFG
1Server1 TimeServer1Server2Server3SLA
2 13:02Value1 Value350
3Date & TimeHeader 13:07Value2 Value450
42019-06-27T13:02-0400Value1
52019-06-27T13:07-0400Value2
6
7Server2
8
9Date & TimeHeader
10
11Server3
12
13Date & Time Header
142019-06-27T13:02-0400Value3
152019-06-27T13:07-0400 Value4
16
17Server4
18
19Date & TimeHeader
202019-06-27T13:02-0400Value5

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,131
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top