Copying sheet from different workbook to target file

[uday]

Hi,

I want to build a Macro where particular sheet will copy from different workbook to target file.
I have attached the macro template herewith where path of the input and target file is mentioned.

In column A the file names are mentioned which are concatenate with patch information in column C. The loop process will open each of the file from given path and copy the sheet from that particular workbook which is also mentioned in column B to target file. The target file path and source file path is mentioned in column c and d.

Source files could be multiple. Loop process will file the last cell value in column C. In case of VBA nullstring msgbox should appear with note.

Regards,
Uday

 

[vletm]

uday
Have You reread Your written text above (#1 Reply) and
compare it with Your sent file?
Of course, something match, but ...

 

[uday]

Cant understand. Do I need to change anything?

 

[vletm]

uday
Did You check Your named 'column A'?
Did You check Your named 'column C'?
Did You check Your named 'path' somewhere?
Did You check Your named 'particular workbook' in 'column B'?

If You would like to get better answers
... then ... You should do something!

 

[uday]

Sorry!!
My bad, vletm. I was attached different workbook. I had check it in first place. I have attached the correct file now.

Thanks & Regards,
Uday

 

[uday]

Hi vletm,

I have wrote the below code. however not getting error . Please help.

Sub test1()

Dim path As String, path2 As String, i As Integer
Dim wb As String

On Error GoTo errh

path = Range("E2").Value

Workbooks.Open Filename:=path

Windows.Application.Workbooks("Macro Template.xlsb").Activate


For i = 2 To Application.CountA(Sheet1.Range("C:C"))
For c = 2 To Application.CountA(Sheet1.Range("A:A"))

path2 = Sheet1.Range("C" & i).Value
wb = Sheet1.Range("A" & i).Value



Workbooks.Open Filename:=path2

Windows.Application.Workbooks("ABC").Activate

Sheets("ABC").Copy After:=Workbooks("wb").Sheets(1)

Next c
Next i


Exit Sub

errh:

MsgBox "Please check if the path is correcty"


End Sub

Thanks & Regards,
Uday

 

[vletm]

uday
Do it work as You have written?

 

[uday]

No, it is not working with the second for next loop process. I am getting error in below mentioned line.

Sheets("ABC").Copy After:=Workbooks("wb").Sheets(1)

Error message - Subscript out of range.

ABC is my target file. where I want to copy the ABC sheet from ABC workbook to Book1.

Regards,
Uday

 

[vletm]

uday
You have written that wb is something
and later You're using "wb" !
Those wb and "wb" could be different and
"wb" will always be ALWAYS SAME.
... do You have there workbook, which name is wb?
... and what to do?
... ... if there is already sheet which You're copying? eg there cannot be many Apples!

 

[uday]

I have written the code in different way to work. It is working now. But still not satisfy. Want to do it in loop process. Please check the below coding and please ignore my reply at 5.12 pm.

Book1, Book2, Book3 should have Mango, Apple, Lichi sheet which will copy to ABC workbook. Can you please help me to perform the below coding in loop process?

Sub test2()

Dim path As String


On Error GoTo errh

Application.DisplayAlerts = False

path = Range("E2").Value
Workbooks.Open Filename:=path
Windows.Application.Workbooks("Macro Template.xlsb").Activate

Workbooks.Open Filename:="D:\Users\Uday.Roy\Documents\Book1.xlsx"
        Sheets("Mango").Copy After:=Workbooks("ABC").Sheets(1)
            Windows.Application.Workbooks("Book1").Save
        Windows.Application.Workbooks("Book1").Close
       
       
    Workbooks.Open Filename:="D:\Users\Uday.Roy\Documents\Book2.xlsx"
         
                 Sheets("Apple").Copy After:=Workbooks("ABC").Sheets("Mango")
                 Windows.Application.Workbooks("Book2").Save
        Windows.Application.Workbooks("Book2").Close
       
       
Workbooks.Open Filename:="D:\Users\Uday.Roy\Documents\Book3.xlsx"
        
                 Sheets("Lichi").Copy After:=Workbooks("ABC").Sheets("Apple")
                 Windows.Application.Workbooks("Book3").Save
        Windows.Application.Workbooks("Book3").Close
                 
    Application.DisplayAlerts = True

Exit Sub

errh:

MsgBox "Please check if everything is just in place"



End Sub

Thanks & Regards,
Uday

 

[vletm]

uday
I've already twice teach You to use code -tags - have You noticed?
I've made some questions - have You noticed? hint: without answers ... yes, without answers
Did You tested that my previous hint?
Why You change - change ... means testing ... testing?
Do You have some idea with Your testings?
How many more variations will You still make before ... thinking one second?

 

[uday]

Sorry for not following the code tags.

Answer to your question related to "wb" (Reply # 10). I wanted to create a variable where I can represent all input file Book1, Book2, Book3 by "wb". It was not working and was showing subscript out of range. I could not find the reason. I wanted to do the whole procedure through loop process where input file will be open one by one and copy the desired sheet from there and paste onto target file that's it!!

I tried to fix the problem with above mentioned code (Reply #11) and it is working. But I wants to do the same thing by applying "for next loop" where each input file will be open and copy the sheet from them and paste onto target file.

If possible can you please help me to do this same activity by loop process.

Thanks & Regards,
Uday

 

[vletm]

uday
Seems that You have skipped my replies with code -tags
as well as
As written in #9 reply:
wb can be different than "wb"!
"wb"s 'value' is ALWAYS "wb"!
Take those "" away around wb!

How to 'help' something
... if You skip replies, which has answer for Your challenge?

 

[uday]

Thank you very much vletm for the suggestion. It is working fine now.