Hi All,
I am getting different files from different customers (in same format) and
saving it in a folder. Now to combine all the data, I am trying to write a
macro (vba code) which open each file in the folder copy the data from
first sheet and paste it in file containing the macro.
By help form lovely people on this forum, I have come upto this code,
pasted underneath but I get an error at this row
wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial xlPasteValues
The code opens the files and copy the required data (which is untill first
blank row in column B). But It is unable to paste.
The error I get is
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed
Can anyone please help me with it.
Many Thanks :)
Sub CombineSheets()
Dim lstRow
Dim Filepath As String
Dim strFilename As String
Dim wsMain As Worksheet
Dim wsRD As Worksheet
Dim wbCountry As Workbook
Dim wsCountry As Worksheet
Set wsMain = Sheets("Main")
Set wsRD = Sheets("RawData")
Filepath = wsMain.Range("C5").Value
lstRow = wsRD.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
strFilename = Dir(Filepath & "\*.xlsx", vbNormal)
If Len(strFilename) = 0 Then Exit Sub
Do Until strFilename = ""
Set wbCountry = Workbooks.Open(Filename:=Filepath & "\" & strFilename)
Set wsCountry = wbCountry.Worksheets(1)
variable = wsCountry.Cells(5, 2).End(xlDown).Row
wsCountry.Rows("5:" & variable).Copy
wbCountry.Close
lstRow = wsRD.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial
xlPasteValues
strFilename = Dir()
Loop
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
