Forum Discussion

Jyothi1984's avatar
Jyothi1984
Copper Contributor
Oct 16, 2022

VBA code for copying from sheets and append it in a new work sheet or another workbook.

I have some data in different worksheets. I need to get all the data appended into a New worksheet or a single master file (workbook). By recording the macro I could do it to some extend. (I have pu...
Macros and VBA
JMB17's avatar
JMB17
Bronze Contributor
Oct 17, 2022

Jyothi1984 

 

Perhaps something like this? This code just copies the worksheets used ranges, but if you want to limit it to only A2:C8, then just change this piece:

 

wksht.UsedRange.Copy

to

wksht.Range("A2:C8").Copy

 

Sub test()
     Dim destWksht As Worksheet
     Dim wksht As Worksheet
     
     Set destWksht = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
     
     For Each wksht In ThisWorkbook.Worksheets
          wksht.UsedRange.Copy destWksht.Cells(destWksht.Rows.Count, 1).End(xlUp).Offset(2, 1)
     Next wksht
     
End Sub

  

  • Nichyy's avatar
    Nichyy
    Copper Contributor
    Dec 29, 2022

    Hi JMB17 i want to copy the data from 1 workbook to another workbook. 

    This is the current code that I am using.

     

    Sub Copy_data_()


    Workbooks("workbook that i wan to copy data.xlsx").Worksheets("worksheet that I wan to copy data").Range("A2:J10000").Copy _
    Workbooks("target workbook.xlsm").Worksheets("target sheet").Range("A2")

    End Sub

     

    my question is, is there any codes that i can use to work with any workbook name that I wan to copy the data. As this code will only apply to specific workbook name and I have to update the workbook name if the workbook name is changed.

Resources