Hi,
You want to combine all files in 1 sheet.
Revert awaited.
Regards
Ashish
On 18 October 2014 18:59, Hasnain Raja <mhr...@gmail.com> wrote:
> 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.
>
--
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.
