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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top