Copy data to another workbook

[ashani]

hi everyone,

i wonder if someone please could guide me - i'm new to VBA and come across with a situation and hoping that one of your expert could help me please. Here is the situation :

- In my workbook I have 6 sheets (different sheet names i.e. A,B,C,D) and in each sheet I have data from Row A2 to however long it goes.

What I want a macro button - when user click on it :

1. Input box to browse and define which file to copy to
2. Copy all the data from each sheet to the same rows in the other sheet with the same sheet name.
3. In the other workbook - those sheets are hidden so unhide > copy > hide.
4. Finally message box to confirm copied

I would really appreciate if someone could help me.

Many thanks,

 

[Zot]

You mean you have:
Workbook ABC with sheets A, B, C, D

You click button macro will run to ask you to select file
The selected workbook (Source) have hidden sheets (A, B, C, D too?)

Copy data from Source to correspond sheets A, B, C, D and acknowledge complete

Will you copy from several workbook and continue to add data to existing ones in Workbook ABC?

 

[ashani]

Hi @Zot
Thank you for the reply. Here’s the answers below :


Worksheets named A, B, C, D

You click button macro will run to ask you to select file
The selected workbook (Source) have hidden sheets (A, B, C, D too?) - yeah that’s correct

Copy data from Source to correspond sheets A, B, C, D and acknowledge complete - yeah that’s correct

Will you copy from several workbook and continue to add data to existing ones in Workbook ABC? No the data will come from one workbook initially and user will add more data in the destination workbook as and when.

thank you once again

 

[Zot]

I have no idea where source data started from and where they are going to be pasted. SO, I just assumed from source sheet A1 to destination sheet A1.

I did not include any error handling if no worksheet by specific name is found though. The source worksheet can be hidden or very hidden (can only be made visible through vba or VB Editor, but it does not matter since the date can be copied even without making sheet visible. This is normal.

Code is simple and you can easily modified it to your need

Sub GetDataCSV()

Dim eRow&
Dim ShtName$, ArryShtName$()
Dim Fname As Variant, NameX As Variant
Dim cell As Range, rngSource As Range
Dim wsSource As Worksheet, wsDest As Worksheet
Dim wbDest As Workbook, wbSource As Workbook

Set wbDest = ActiveWorkbook

' Sheet name list here as string separated by comma. To be known existed in woekbook source
ShtName = "A,B,C,D"
' Convert into array
ArryShtName = Split(ShtName, ",")

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.csv: *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then                          'CANCEL is clicked
    Exit Sub
End If

Set wbSource = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

For Each NameX In ArryShtName
    Set wsSource = wbSource.Sheets(NameX)
    Set wsDest = wbDest.Sheets(NameX)
    ' Get last data row in source
    eRow = wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row
    wsSource.Range("A1", "A" & eRow).Copy Destination:=wsDest.Range("A1")
Next

wbSource.Close False

End Sub

 

[ashani]

Hi @Zot

Thank you so much for taking time out to help me. Really appreciate that.

I have just tried to run the code but unfortunately I'm getting an error message "Subscription out of Range" and the following line is highlighted :

    Set wsSource = wbSource.Sheets(NameX)

In regards to source data - so for example in Sheet A - Source data starts from A1 - G33 and going to be pasted in exactly the same location in destination sheet with the same sheet name
Sheet B - Source data starts from A10 - K22 and going to be pasted in exactly the same location in destination sheet with the same sheet name
Sheet C - Source data starts from A8 - G53
so on

Thank you once again and really appreciate your guidance.

 

[Zot]

It is because it cannot find the sheet name it is looking for. Did you put the sheet name right?

My sample use ShtName = "A,B,C,D". You have to replace accordingly if the name is not ABCD. Say like

ShtName= "Apple,Orange,Mango,Banana"

Note no space and double quote only at both ends.

 

[ashani]

It is because it cannot find the sheet name it is looking for. Did you put the sheet name right?

My sample use ShtName = "A,B,C,D". You have to replace accordingly if the name is not ABCD. Say like

ShtName= "Apple,Orange,Mango,Banana"

Note no space and double quote only at both ends.

Thanks @Zot

How can i describe this in the code

Sheet A - Source data starts from A1 - G33 and going to be pasted in exactly the same location in destination sheet with the same sheet name
Sheet B - Source data starts from A10 - K22 and going to be pasted in exactly the same location in destination sheet with the same sheet name
Sheet C - Source data starts from A8 - G53
so on

 

[ashani]

Hi @Zot

Thank you so much - all sorted and works perfectly.

Really appreciate your help.

 

[Zot]

Hi @Zot

Thank you so much - all sorted and works perfectly.

Really appreciate your help.

So you figured out how to modify the code. My code just repeat 1he same starting cell A1.

Glad to hear it worked