copy from offline data

[koi]

Hi Guru's,

is there any way to copy multiple source from offline to master workbook?

let say i have 100 excel file with same format in C:\\mydocument\, i want to copy from :
1. C26 : S & Last Row (last row is column C), it could be C30 or C55 as last row
2. i need to copy from D20 at the same time to B26 till B last row (last row same as above) in the master sheet

when i paste to the master sheet, the C26 : S from source goes to C26 : S in the master sheet, and D20 from source goes to B26 : B (last row) on the master sheet as well

Thanks for helping me

 

[Marc L]

Hi !

« offline » ?‼ A sample in the thread

macro to copy closed excel files to a master sheet in same folder …
​

 

[koi]

hi Marc,

could you explain to me a bit of the code? and i need the last row function when copying from source to master, Thanks

 

[Marc L]

The code uses the very easy Range.Copy method …
See also in VBA help UsedRange property !

There is no generic « last row function » !
Four ways at least but depending on real data worksheet …
​

 

[koi]

hi, i need something like below..but i want to start copy from C26 to S & LastRow (or erow in this code), is that possible? Thanks

Sub copyDataFromMultipleWorkbooksIntoMaster()

Dim FolderPath As String, Filepath As String, Filename As String

FolderPath = “C:\work\excel_tutorial\suppliers\”

Filepath = FolderPath & “*.xlsx”

‘To transfer data from all files you can use the wild-card character *

‘Filepath = FolderPath & “*.xls*”

Filename = Dir(Filepath)

Dim lastrow As Long, lastcolumn As Long

Do While Filename <> “”
Workbooks.Open (FolderPath & Filename)
‘Range(“A2:D2”).Copy
lastrow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row

‘If we wanted to paste data of more than 4 columns we would define a last column here also
‘lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
‘ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1),
Cells(erow, lastcolumn))
ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1),
Cells(erow, 4))

Filename = Dir

Loop

End Sub

 

[Marc L]

Range.Copy method : SourceRange.Copy DestinationRange …

Example : Range("Sheet1!A11:C20").Copy Range("Sheet2!Z51")
​

 

[koi]

Hi Marc,

could you write more detail where to change from above code? Thanks

 

[Marc L]

Amend source range in Copy codeline
and destination range in Paste codeline …

But as I yet wrote, it depends on real data workbooks
and as I can't guess …

See also the sample in the thread link.
​

 

[koi]

Hi,

please find sample, i want to capture the important number on column B on Master.xlsm and from C26:S &LastRow from source to Master.xlsm as well

put all the source into 1 folder, so that we can grab the folder path
put workbook open for each source, clear background if copied large file, close workbook without saving it

that is all the intention for the macro.

Thanks for helping

 

[Marc L]

Unclear as column B is empty whatever the workbook
and your master workbook is empty !
It is not difficult to fill it according to source files and join it …
​

 

[koi]

hi Marc,

this code almost do the trick if we setup the fixed range example C26:S36 then it gather all things perfectly, it is only missing the last row option and to copy the important number, can you please try to give it a go? Thanks

Function RDB_Last(choice As Integer, rng As Range)
' By Ron de Bruin, 5 May 2008
' A choice of 1 = last row.
' A choice of 2 = last column.
' A choice of 3 = last cell.
Dim lrw As Long
Dim lcol As Integer

Select Case choice

Case 1:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
after:=rng.cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

Case 2:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
after:=rng.cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
after:=rng.cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

On Error Resume Next
lcol = rng.Find(What:="*", _
after:=rng.cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

On Error Resume Next
RDB_Last = rng.Parent.cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
RDB_Last = rng.cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0

End Select
End Function

 

[Marc L]

Do not need any gas factory code !

But I still need you clarify this :

i want to capture the important number on column B on Master.xlsm

… or better just attach a result workbook according to source workbooks !
​

 

[koi]

Hi Marc,

attached result that i want, Thanks

 

[Marc L]

Try this demonstration :​

Sub Demo()
    FOLDER$ = ThisWorkbook.Path & "\"
    Sheet1.UsedRange.Offset(2).Clear
    Application.ScreenUpdating = False
      R& = 3
      F$ = Dir(FOLDER & "*.xlsx")
While F > ""
    With GetObject(FOLDER & F).Worksheets(1).UsedRange.Rows
        If .Count > 25 Then
            N& = N& + 1
            L& = .Count - 25
            .Cells(20, 4).Copy Sheet1.Cells(R, 1).Resize(L)
            .Item("26:" & .Count).Columns("C:S").Copy Sheet1.Cells(R, 2)
            R = R + L
        End If
            .Parent.Parent.Close False
    End With
      F = Dir
Wend
    Application.ScreenUpdating = True
    MsgBox N & " worksheet" & IIf(N > 1, "s", "") & " loaded", vbInformation, " Done !"
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[koi]

Amazing !!! thanks Marc for the support even though this kind of code is unusual from what I normally know but the result is very good and speed is very fast

Thanks a lot, really appreciate it !