I have two columns Data and Data1, on worksheet named "Data". I have same column names on sheet named "MasterData". I want to read the stuff on "Data" worksheet and copy to "MasterData" based on the column names (Data & Data1). Also, let's say I have 10 data points that I copied over to "MasterData", next time I want to keep that data there, but copy new data from sheet "Data" to "MasterData", by checking for the 1st empty cell (which in this case would be Cell Number 11) in specific column. This would need to go on as "MasterData" would store all the historical data.
Sub CopyDatatoMasterData2()
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "D").End(xlUp).Row
Range("D" & lMaxRows + 1).Select
ActiveSheet.Paste
Range("E2").Select
Sheets("Data").Select
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
ActiveSheet.Paste
Range("F2").Select
Sheets("Data").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "F").End(xlUp).Row
Range("F" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "G").End(xlUp).Row
Range("G" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "H").End(xlUp).Row
Range("H" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "I").End(xlUp).Row
Range("I" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "J").End(xlUp).Row
Range("J" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "K").End(xlUp).Row
Range("K" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "L").End(xlUp).Row
Range("L" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("M2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "M").End(xlUp).Row
Range("M" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("N2:N3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "N").End(xlUp).Row
Range("N" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("O2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "O").End(xlUp).Row
Range("O" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("P2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "P").End(xlUp).Row
Range("P" & lMaxRows + 1).Select
ActiveSheet.Paste
Sheets("Data").Select
Range("Q2:Q3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MasterData").Select
lMaxRows = Cells(Rows.Count, "Q").End(xlUp).Row
Range("Q" & lMaxRows + 1).Select
ActiveSheet.Paste
End Sub
This is not a good question for Stack Overflow. This site is for programmers to help one another develop. Your question reads as: “I want a macro to do this. Write it for me.” Not a popular style of question.
Top right of this page you will find a Help button. You will find lots of good, but general, advice behind it. In this answer I will try to give more specific advice.
You must learn the basics of VBA. You could have answered this question yourself with very little knowledge. A few hours spent on study will quickly be repaid. Search the web for “Excel VBA tutorial”. There are many to choose from. Try a few and complete the one that is closest to your learning style. I prefer books. I visited a good library; reviewed their Excel VBA Primers; borrowed the most promising to try at home before buying the one I considered to be best as a permanent reference book.
This site is not a good place to ask design questions. You need to break your requirement down its little steps. There are lots questions and answers here that address the individual steps of your total requirement but, as far as I know, not one that matches your complete requirement.
You can often create a simple design just by thinking through how you would solve this problem manually:
There are VBA statements that are the equivalent of steps 1 to 4. You can select and copy as separate steps in VBA but this is not good practice; there is a VBA statement that combines them which is faster and neater.
There are several VBA statements that allow you to repeat (loop) blocks of code with different parameters to achieve slightly different effects.
Doesn’t that look easier already? Instead of a vague description, we have a series of easy steps. I would expect any tutorial to explain how to perform these steps within the first few pages. In my code I use a few techniques that may not be at the beginning of a tutorial. I explain why I use them. For extra information, search for “Excel VBA function array” or “Excel VBA constants” or similar. Come back with questions if necessary but the more you can discover for yourself, the faster you will develop.
Welcome to the joys of programming.
Option Explicit
' * I have a system of naming my variabls that I have used for years. I can
' look at macros I wrote years ago and immediately know what all the
' variables. I am not asking you to like my system but to develop your own
' system.
' * My naming system using a sequence of words that get more and precise
' until I have a unique name. The first word what the variable is being
' used for. "Col" means its associated with columns of a worksheet or a
' two-dimensional array. If I have multiple worksheets and/or arrays, the
' next word identifies whch worksheet or array. If I have multiple
' columns, the next word identifies which column.
' * Constants make code easier to read and easiest to maintain. Instead of a
' literal, such as 2, you have a meaningful name, ColDataData. Columns
' can be identified by code or number: A=1, B=2, C=3 and so on. I have used
' numbers which I normally find more convenient although I could have used
' codes here.
Const ColDataData As Long = 2
Const ColDataData1 As Long = 4
Const ColMasterData As Long = 1
Const ColMasterData1 As Long = 2
Const RowDataFirstData As Long = 2 ' Adjust according to how many header
' rows you have in worksheet "Data"
Sub AddToMasterColumn()
Dim ColDataCrnt As Long
Dim ColMasterCrnt As Long
Dim ColsData() As Variant
Dim ColsMaster() As Variant
Dim InxCols As Long
Dim Rng As Range
Dim RowDataLast As Long
Dim RowMasterTgt As Long
Dim WshtData As Worksheet
Dim WshtMaster As Worksheet
' * Processing for your two sets of columns is the same so we can use a loop
' with parameters so says which pair of columns each loop is to handle.
' * Array() is a convenient way to load an array although the array must be
' variant. Since I am loading different data types (String and Long) to
' the same array, this is required anyway.
' * These two arrays MUST have the same number of elements.
ColsData = Array(ColDataData, ColDataData1)
ColsMaster = Array(ColMasterData, ColMasterData1)
' Debug.Assert is a convenient way of testing for program errors during
' development. If UBound(ColsData) and UBound(ColsMaster) are not equal,
' execution will stop here.
Debug.Assert UBound(ColsData) = UBound(ColsMaster)
' I could have used Worksheets("Data") where ever I use WshtData below.
' Using WshtData is slightly faster and, in my view, neater. If you change
' names of one of the worksheets, one change here will fix the macro.
Set WshtData = Worksheets("Data")
Set WshtMaster = Worksheets("MasterData")
For InxCols = LBound(ColsData) To UBound(ColsData)
With WshtMaster
' Get the destination column in worksheet Master for the current loop
ColMasterCrnt = ColsMaster(InxCols)
' Find the last used row in ColMasterCrnt and add one to it to get the target cell
RowMasterTgt = .Cells(Rows.Count, ColMasterCrnt).End(xlUp).Row + 1
End With
With WshtData
' Get the source column in worksheet data for the current loop
ColDataCrnt = ColsData(InxCols)
' Find the last used row in ColDataCrnt
RowDataLast = .Cells(Rows.Count, ColDataCrnt).End(xlUp).Row
' Specify the range to be copied
Set Rng = .Range(.Cells(RowDataFirstData, ColDataCrnt), .Cells(RowDataLast, ColDataCrnt))
End With
' Copy data
Rng.Copy Destination:=WshtMaster.Cells(RowMasterTgt, ColMasterCrnt)
Next
End Sub
Additon in response to extra requirement
I believe Function FindColumnByName() does all that you require. I have more complicated variations which I can provide if necessary. For example I have a variation that searches for multi-row headings. But let us start simple.
You will need to delete:
Const ColDataData As Long = 2
Const ColDataData1 As Long = 4
Const ColMasterData As Long = 1
Const ColMasterData1 As Long = 2
and add at the top of AddToMasterColumn:
Dim ColDataData As Long:
Dim ColDataData1 As Long
Dim ColMasterData As Long
Dim ColMasterData1 As Long
and add after WshtData and and WshtMaster are set:
ColDataData = FindColumnByName(WshtData, “Data”)
ColDataData1 = FindColumnByName(WshtData, “Data1”)
ColMasterData = FindColumnByName(WshtMaster, “Data”)
ColMasterData1 = FindColumnByName(WshtMaster, “Data2”)
The test routine TestFCBN shows techniques that might be useful or might be confusing at this stage of your development. Have a quick look but ignore it until later if it you have any difficulty understanding it. With my test worksheets it outputs:
Column 2 of worksheet Data is named Data
Column 4 of worksheet Data is named Data1
There is no column named X in worksheet Data
Column 1 of worksheet MasterData is named Data
Column 2 of worksheet MasterData is named Data1
New code:
Sub TestFCBN()
Dim ColName As String
Dim ColTgt As Long
Dim InxTV As Long
Dim TestValues As Variant
Dim WshtName As String
TestValues = Array("Data", "Data", "Data", "Data1", "Data", "X", _
"MasterData", "Data", "MasterData", "Data1")
' There must be an even number of values in TestValues. The first of each
' pair is the name of a worksheet, the second the name of a column in that
' worksheet.
For InxTV = LBound(TestValues) To UBound(TestValues) Step 2
WshtName = TestValues(InxTV)
ColName = TestValues(InxTV + 1)
ColTgt = FindColumnByName(Worksheets(WshtName), ColName)
If ColTgt = 0 Then
Debug.Print "There is no column named " & ColName & " in worksheet " & WshtName
Else
Debug.Print "Column " & ColTgt & " of worksheet " & WshtName & _
" is named " & ColName
End If
Next
End Sub
Function FindColumnByName(Wsht As Worksheet, ColName As String) As Long
' This function scans row 1 for a cell with a value of ColName.
' If found, it returns the number of that column.
' If not found, it returns 0
Dim ColCrnt As Long
Dim ColLast As Long
With Wsht
ColLast = .Cells(1, Columns.Count).End(xlToLeft).Column
For ColCrnt = 1 To ColLast
If .Cells(1, ColCrnt).Value = ColName Then
FindColumnByName = ColCrnt
Exit Function
End If
Next
End With
FindColumnByName = 0
End Function