×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

combining data from other worksheets to master sheet

combining data from other worksheets to master sheet

combining data from other worksheets to master sheet

(OP)
Hi.. I have a bunch of data in the form of worksheets in a folder. ALL of the worksheets have the same format. The relevant data i need in each is from c6 to c307.

This folder is continuously updated with newer worksheets. Each newer worksheet added into this folder has a number that is higher than those inside the folder, although not necessarily consecutive.

I also have a master file, in which i compile the data from each worksheet.

I need a macro that when i run it, finds the newest worksheet in the folder, adds the data from c6:c307 it to the master file. each time data is added, it has to go to the next open column, and stay consistent in that each first row is the value from c6. ( i need the consistency in rows because i already have an avg macro that should run after the next column is added.)

Is there some way to do this? I've already recorded a couple of macros that do this, but each time its for a specific file and puts the data in the specific place. How do i add the "+1" clause? Should i put an if then statement or two?
I basically need the macro to scan the folder, find the files that aren't already in the master, and take the c6:c307 data from them, and put that data into the next available column.

Let me know if anything doesn't make sense.

Anything at all would help.. I'm fairly new to all of this.

 

RE: combining data from other worksheets to master sheet

The most recent file in a folder can be found using the FileSystemObject:

CODE

Function MostRecentFile(fldr)
 'Reference: Microsoft Scripting Runtime
 Dim f As File, fl As Folder, fs As FileSystemObject
 Dim fd As Date
 Dim fname As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fl = fs.GetFolder("C:\Docs")
    
    For Each f In fl.Files
        If IsNull(fd) Or f.DateCreated > fd Then
            fd = f.DateCreated
            fname = f.name
        End If
    Next
    MostRecentFile = fname
End Function

RE: combining data from other worksheets to master sheet

Quote (LBTaurus):

This folder is continuously updated with newer worksheets.

Do you only need to add the latest file or do you need to add all those files that have not yet been added to the master? If you need all the sheets that have not yet been added it will complicate things, you would need some way to record which sheet has/has not been recorded. Also, what happens when you run out of columns in the master? Add them to sheet 2? Delete older data? Start adding data to lower rows? I realize that there are a lot of columns on a sheet, but eventually you will run out.

RE: combining data from other worksheets to master sheet

There really aren't a lot of columns if you are not using Excel 2007.  Any earlier version has a limit of 256 columns.

-handleman, CSWP (The new, easy test)

RE: combining data from other worksheets to master sheet

(OP)
First off... Thanks guys. very helpful

Remou... where in my macro should this snippet be going? Will this be in the beginning? Also, will this just find the most recent file or files? Again, im fairly new to excel VBA.

Cowski... I'm using 2007... I think at max there will be about 40 new files a month. i believe there are 16k columns in it, so its not too huge of a concern. We also dont want to run into the problem of having too much data per worksheet, for accessability reasons, so  after every hundred or so data sets we'll change to a new file.

I'll post my macro that i have as of now in a just a bit.  

RE: combining data from other worksheets to master sheet

(OP)
Sub SearchAdd()
'
' SearchAdd Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\Users\ketan.dhawan\Desktop\Mandrel Tests\4935W01.xls;DefaultDir=C:\Users\ketan.dhawan\Desktop\Mandrel Te" _
        ), Array("sts;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination _
        :=Range("$U$6")).QueryTable
        .CommandText = Array( _
        "SELECT `Data$`.`OM-CP-PROCESS101`" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\ketan.dhawan\Desktop\Mandrel Tests\4935W01.xls`.`Data$` `Data$`" & Chr(13) & "" & Chr(10) & "WHERE (`Data$`.`OM-CP-PROCESS101` Not Like '%Channel 1%' And `Data$`.`OM-CP-PROCESS10" _
        , _
        "1` Not Like '%Current Recorder%' And `Data$`.`OM-CP-PROCESS101` Not Like '%M93958%' And `Data$`.`OM-CP-PROCESS101` Not Like '%Proc%')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Excel_Files_1"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 172
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 177
    ActiveWindow.ScrollRow = 178
    ActiveWindow.ScrollRow = 181
    ActiveWindow.ScrollRow = 184
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 188
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 192
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 194
    ActiveWindow.ScrollRow = 195
    ActiveWindow.ScrollRow = 196
    ActiveWindow.ScrollRow = 198
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 201
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 205
    ActiveWindow.ScrollRow = 208
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 213
    ActiveWindow.ScrollRow = 215
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 220
    ActiveWindow.ScrollRow = 221
    ActiveWindow.ScrollRow = 222
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 227
    ActiveWindow.ScrollRow = 229
    ActiveWindow.ScrollRow = 230
    ActiveWindow.ScrollRow = 233
    ActiveWindow.ScrollRow = 235
    ActiveWindow.ScrollRow = 236
    ActiveWindow.ScrollRow = 238
    ActiveWindow.ScrollRow = 240
    ActiveWindow.ScrollRow = 241
    ActiveWindow.ScrollRow = 243
    ActiveWindow.ScrollRow = 245
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 248
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 250
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 254
    ActiveWindow.ScrollRow = 255
    ActiveWindow.ScrollRow = 256
    ActiveWindow.ScrollRow = 257
    ActiveWindow.ScrollRow = 258
    ActiveWindow.ScrollRow = 259
    ActiveWindow.ScrollRow = 261
    ActiveWindow.ScrollRow = 263
    ActiveWindow.ScrollRow = 264
    ActiveWindow.ScrollRow = 266
    ActiveWindow.ScrollRow = 267
    ActiveWindow.ScrollRow = 268
    ActiveWindow.ScrollRow = 270
    ActiveWindow.ScrollRow = 273
    ActiveWindow.ScrollRow = 275
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 280
    ActiveWindow.ScrollRow = 282
    ActiveWindow.ScrollRow = 283
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 286
    ActiveWindow.ScrollRow = 288
    ActiveWindow.ScrollRow = 283
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 275
    ActiveWindow.ScrollRow = 269
    ActiveWindow.ScrollRow = 268
    ActiveWindow.ScrollRow = 261
    ActiveWindow.ScrollRow = 255
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 244
    ActiveWindow.ScrollRow = 238
    ActiveWindow.ScrollRow = 230
    ActiveWindow.ScrollRow = 227
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 217
    ActiveWindow.ScrollRow = 214
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 196
    ActiveWindow.ScrollRow = 190
    ActiveWindow.ScrollRow = 188
    ActiveWindow.ScrollRow = 184
    ActiveWindow.ScrollRow = 181
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 178
    ActiveWindow.ScrollRow = 181
    ActiveWindow.ScrollRow = 185
    ActiveWindow.ScrollRow = 190
    ActiveWindow.ScrollRow = 191
    ActiveWindow.ScrollRow = 196
    ActiveWindow.ScrollRow = 198
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 208
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 214
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 220
    ActiveWindow.ScrollRow = 224
    ActiveWindow.ScrollRow = 227
    ActiveWindow.ScrollRow = 228
    ActiveWindow.ScrollRow = 231
    ActiveWindow.ScrollRow = 234
    ActiveWindow.ScrollRow = 235
    ActiveWindow.ScrollRow = 238
    ActiveWindow.ScrollRow = 239
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 245
    ActiveWindow.ScrollRow = 246
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 252
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 254
    ActiveWindow.ScrollRow = 256
    ActiveWindow.ScrollRow = 257
    ActiveWindow.ScrollRow = 258
    ActiveWindow.ScrollRow = 259
    ActiveWindow.ScrollRow = 260
    ActiveWindow.ScrollRow = 261
    ActiveWindow.ScrollRow = 263
    ActiveWindow.ScrollRow = 265
    ActiveWindow.ScrollRow = 267
    ActiveWindow.ScrollRow = 269
    ActiveWindow.ScrollRow = 270
    ActiveWindow.ScrollRow = 271
    ActiveWindow.ScrollRow = 272
    ActiveWindow.ScrollRow = 273
    ActiveWindow.ScrollRow = 274
    ActiveWindow.ScrollRow = 275
    ActiveWindow.ScrollRow = 276
    ActiveWindow.ScrollRow = 275
    ActiveWindow.ScrollRow = 276
    ActiveWindow.ScrollRow = 277
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 279
    ActiveWindow.ScrollRow = 280
    ActiveWindow.ScrollRow = 281
    ActiveWindow.ScrollRow = 282
    ActiveWindow.ScrollRow = 283
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 286
    ActiveWindow.ScrollRow = 288
    Range("U308:U330").Select
    Selection.ClearContents
    ActiveWindow.ScrollRow = 287
    ActiveWindow.ScrollRow = 286
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 284
    ActiveWindow.ScrollRow = 283
    ActiveWindow.ScrollRow = 282
    ActiveWindow.ScrollRow = 279
    ActiveWindow.ScrollRow = 276
    ActiveWindow.ScrollRow = 275
    ActiveWindow.ScrollRow = 271
    ActiveWindow.ScrollRow = 269
    ActiveWindow.ScrollRow = 262
    ActiveWindow.ScrollRow = 256
    ActiveWindow.ScrollRow = 250
    ActiveWindow.ScrollRow = 246
    ActiveWindow.ScrollRow = 243
    ActiveWindow.ScrollRow = 234
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 221
    ActiveWindow.ScrollRow = 213
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
End Sub




SO all of the active window stuff is garbage from what i can gather. This just went into the folder, took out the file using query, and also got rid of the useless data. It was put into u6 because that was the next available column.

I finished by deleting the data beyond 300 readings.   

RE: combining data from other worksheets to master sheet

You can paste this code into a module in a copy of the master worksheet to test. It should copy c6:c307 into a new column from the newest file.

CODE

Function MostRecentFile(fldr)
 'This uses late binding, so a reference
 'is not required.
 'Note that there is no error coding - it
 'should be added.

 Dim f As Object, fl As Object, fs As Object
 Dim fd As Date
 Dim fname As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fl = fs.GetFolder(fldr)
    
    'Go through each file in the folder
    For Each f In fl.Files
        'If we haven't got a file yet ot the
        'file is newer
        If IsNull(fd) Or f.DateCreated > fd Then
            fd = f.DateCreated
            'save the name
            fname = f.Name
        End If
    Next
    'return value
    MostRecentFile = fname
End Function

Sub GetData()
'Run this procedure to update the file
Dim strFile, LastCol

'Gets the most recent file by calling
'the procedure above.
strFile = MostRecentFile("c:\docs\dat\")

'Connection string to most recent file
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
    
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'The data will be got from sheet1, range c6:c307
strSQL = "SELECT * FROM [Sheet1$C6:C307]"

rs.Open strSQL, cn

'Find the last column, if you mess with the columns or add
'new columns this will return the wrong answer, so
'save before running.
LastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

'Copy the data we picked up from the most recent file
'to the last column +1, ie, new column.
ActiveSheet.Cells(6, LastCol + 1).CopyFromRecordset rs

End Sub

RE: combining data from other worksheets to master sheet

(OP)
remou!!

Thanks a whole lot. THis is working well with a few exceptions.

i used only the code you gave me in a new module.

first of all, i replaced fldr in line 12 with my folder location that has the files i want.

I also replaced c:\docs\dat\ in line 32 with this same folder.

I also replaced sheet1 with data  ( line 44) because that is what the sheet is called in the files that contain the values i want.

Is this correct?

what happens when i run it is that it immediately looks in my docs folder and cant find a specific file. Why is it looking for a specific file? an why in my documents folder instead of the one i specified?

The specific file it tries to find happens to be the oldest ( not most recent) one in the folder i told it look in, but the error it gives me says its looking in an entirely different place ( my docs folder).  

Just for kicks, i put the file it kept looking for into my docs folder, and it ran perfectly. However, when it pastes the new values into my master file, it pastes numbers as text. This makes each entry box have a little green tab next to it telling me that it is a number stored as text.

HOw do i fix these problems?

Thanks so much for your help...

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources