cathleenturner
New Member
- Joined
- Feb 4, 2014
- Messages
- 2
Hello All
I have to get raw data from many files in 'Sheet 1'. The structure is generally the same, because the raw data follows the column with the word DIC, but it is not in the same location. How can I use a function (such as find) to
1) search for the column containing the word DIC and
2) select the column containing the word DIC and the three columns next to it, as well?
3) Copy what I have selected and paste into the masterfile
I need to copy this data and paste it into my master file, but I already have that part worked out (I am adapting code I have written for another purpose). I would like to use it in my module (VBA?). I am learning how to write code in VBA, and so far its okay. This website is very helpful.
I am using excel 2010
Thanks in advance!
Cheers,
Cat
------
The code I will be 'adapting' for this new purpose:
Sub copypastaalkdata()
' This is to get data from the worksheet name "WS2"
'and to past it into one master file
'Set the path of the workbook where you will save all data
Dim wb1 As Workbook
Set wb1 = Workbooks.Open("C:\Users\Cathleen\Google Drive\Raw Alk data\Converted_Raw_Alk_files\MasterFileALK.xlsx")
'Set the path of where you should be getting your data from
Dim folderpath As String
Dim filename As String
Dim wb As Workbook
folderpath = "C:\Users\Cathleen\Google Drive\Raw Alk data\Ready\"
If Right(folderpath, 1) <> "\" Then folderpath = "\"
filename = Dir(folderpath & "*.xlsx")
Do While filename <> ""
Dim pathname As String
usethisfile = folderpath & filename
Dim wb2 As Excel.Workbook
Set wb2 = Workbooks.Open(usethisfile)
Application.ScreenUpdating = False
'1.) Determine where the data will go in the masterfile, which is the last row of existing data
wb1.Activate
Dim lastRow As String
lastRow = wb1.Worksheets("pastehere").Cells(Rows.Count, "B").End(xlUp).Row + 1
wb1.Worksheets("pastehere").Range("B" & lastRow).Select
'2.) Activate the sourcebook, aka where you'll get your data from
wb2.Activate
Application.CutCopyMode = False 'to clear clipboard to maximize memory
'i) Get the filename
Dim strFileFullName As String
strFileFullName = wb2.FullName
'3.) Copy from specified range in wb2
wb2.Worksheets("WS2").Range("A2:D50").Copy
'4.)Paste in masterfile
wb1.Worksheets("pastehere").Range("B" & lastRow).PasteSpecial Paste:=xlPasteValues
'5.)put filename into into the masterfile, and save
wb1.Worksheets("pastehere").Range("A" & lastRow).Value = strFileFullName
wb1.Save
'd.) close the file you are getting the alk data from and move on to the next one
wb2.Close
Set wb2 = Nothing
filename = Dir
Loop
Application.ScreenUpdating = True
End Sub
I have to get raw data from many files in 'Sheet 1'. The structure is generally the same, because the raw data follows the column with the word DIC, but it is not in the same location. How can I use a function (such as find) to
1) search for the column containing the word DIC and
2) select the column containing the word DIC and the three columns next to it, as well?
3) Copy what I have selected and paste into the masterfile
I need to copy this data and paste it into my master file, but I already have that part worked out (I am adapting code I have written for another purpose). I would like to use it in my module (VBA?). I am learning how to write code in VBA, and so far its okay. This website is very helpful.
I am using excel 2010
Thanks in advance!
Cheers,
Cat
------
The code I will be 'adapting' for this new purpose:
Sub copypastaalkdata()
' This is to get data from the worksheet name "WS2"
'and to past it into one master file
'Set the path of the workbook where you will save all data
Dim wb1 As Workbook
Set wb1 = Workbooks.Open("C:\Users\Cathleen\Google Drive\Raw Alk data\Converted_Raw_Alk_files\MasterFileALK.xlsx")
'Set the path of where you should be getting your data from
Dim folderpath As String
Dim filename As String
Dim wb As Workbook
folderpath = "C:\Users\Cathleen\Google Drive\Raw Alk data\Ready\"
If Right(folderpath, 1) <> "\" Then folderpath = "\"
filename = Dir(folderpath & "*.xlsx")
Do While filename <> ""
Dim pathname As String
usethisfile = folderpath & filename
Dim wb2 As Excel.Workbook
Set wb2 = Workbooks.Open(usethisfile)
Application.ScreenUpdating = False
'1.) Determine where the data will go in the masterfile, which is the last row of existing data
wb1.Activate
Dim lastRow As String
lastRow = wb1.Worksheets("pastehere").Cells(Rows.Count, "B").End(xlUp).Row + 1
wb1.Worksheets("pastehere").Range("B" & lastRow).Select
'2.) Activate the sourcebook, aka where you'll get your data from
wb2.Activate
Application.CutCopyMode = False 'to clear clipboard to maximize memory
'i) Get the filename
Dim strFileFullName As String
strFileFullName = wb2.FullName
'3.) Copy from specified range in wb2
wb2.Worksheets("WS2").Range("A2:D50").Copy
'4.)Paste in masterfile
wb1.Worksheets("pastehere").Range("B" & lastRow).PasteSpecial Paste:=xlPasteValues
'5.)put filename into into the masterfile, and save
wb1.Worksheets("pastehere").Range("A" & lastRow).Value = strFileFullName
wb1.Save
'd.) close the file you are getting the alk data from and move on to the next one
wb2.Close
Set wb2 = Nothing
filename = Dir
Loop
Application.ScreenUpdating = True
End Sub
Last edited: