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

M

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.

[TABLE="width: 138"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Server1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[/TR]
[TR]
[TD]Date1[/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]Date2[/TD]
[TD]Value2[/TD]
[/TR]
[TR]
[TD]Date3[/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Date4[/TD]
[TD]Value4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[/TR]
[TR]
[TD]Date1[/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]Date2[/TD]
[TD]Value2[/TD]
[/TR]
[TR]
[TD]Date3[/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Date4[/TD]
[TD]Value4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Server3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Server4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[/TR]
[TR]
[TD]Date1[/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]Date2[/TD]
[TD]Value2[/TD]
[/TR]
[TR]
[TD]Date3[/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Date4[/TD]
[TD]Value4[/TD]
[/TR]
</tbody>[/TABLE]

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):
[TABLE="width: 275"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Header2[/TD]
[TD]Header2[/TD]
[TD]Header2[/TD]
[TD]Header2[/TD]
[/TR]
[TR]
[TD]Value1[/TD]
[TD]Value1[/TD]
[TD][/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]Value2[/TD]
[TD]Value2[/TD]
[TD][/TD]
[TD]Value2[/TD]
[/TR]
[TR]
[TD]Value3[/TD]
[TD]Value3[/TD]
[TD][/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Value4[/TD]
[TD]Value4[/TD]
[TD][/TD]
[TD]Value4[/TD]
[/TR]
</tbody>[/TABLE]



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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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:
[TABLE="width: 275"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Server1[/TD]
[TD]Server2[/TD]
[TD]Server3[/TD]
[TD]Server4[/TD]
[/TR]
[TR]
[TD]Value1[/TD]
[TD]Value1[/TD]
[TD][/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]Value2[/TD]
[TD]Value2[/TD]
[TD][/TD]
[TD]Value2[/TD]
[/TR]
[TR]
[TD]Value3[/TD]
[TD]Value3[/TD]
[TD][/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Value4[/TD]
[TD]Value4[/TD]
[TD][/TD]
[TD]Value4[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
moramramesh said:
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:
[TABLE="width: 275"]
<tbody>[TR]
[TD]Server1[/TD]
[TD]Server2[/TD]
[TD]Server3[/TD]
[TD]Server4[/TD]
[/TR]
[TR]
[TD]Value1[/TD]
[TD]Value1[/TD]
[TD][/TD]
[TD]Value1[/TD]
[/TR]
[TR]
[TD]Value2[/TD]
[TD]Value2[/TD]
[TD][/TD]
[TD]Value2[/TD]
[/TR]
[TR]
[TD]Value3[/TD]
[TD]Value3[/TD]
[TD][/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Value4[/TD]
[TD]Value4[/TD]
[TD][/TD]
[TD]Value4[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
Server1 TimeServer1Server2Server3SLA
13:02Value1 Value350
Date & TimeHeader 13:07Value2 Value450
2019-06-27T13:02-0400Value1
2019-06-27T13:07-0400Value2
Server2
Date & TimeHeader
Server3
Date & Time Header
2019-06-27T13:02-0400Value3
2019-06-27T13:07-0400 Value4
Server4
Date & TimeHeader
2019-06-27T13:02-0400Value5

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]

</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Extract Date sorted list from multiple columns stored in a table
Replies
5
Views
366
Fluff
Fluff
T
  • Question Question
What formula is required to find and add values from a matching data set?
Replies
3
Views
471
Fluff
Fluff
B
  • Question Question
split 1 column into multiple columns
Replies
7
Views
891
sandy666
sandy666
A
  • Question Question
Return Heading Value (Title) to multiple Columns
Replies
2
Views
534
Fluff
Fluff
D
  • Question Question
Insert column and past from input box
Replies
3
Views
481
JLGWhiz
JLGWhiz

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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