Yes exactly.
On Saturday, 18 October 2014 14:29:09 UTC+1, Hasnain Raja 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
