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 put the code below). In this code I have considered 4 sheets to be copied to a new workbook as an example.

But understand that there are still missing something. 

How could I modify the code to check the total number of sheets in the workbook (as the number of worksheets in each file may vary.)

 

Sub copydata()
Range("A2:C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("master.xlsx").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2:C8").Copy
Windows("master.xlsx").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("for copy.xlsm").Activate
Sheets("Det-2").Select
Range("A2:C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("master.xlsx").Activate
Range("A9").Select
ActiveSheet.Paste
Windows("for copy.xlsm").Activate
Sheets("Det-3").Select
Range("A2:C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("master.xlsx").Activate
Range("A16").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=15
Windows("for copy.xlsm").Activate
Sheets("Det-4").Select
Range("A2:C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("master.xlsx").Activate
Range("A23").Select
ActiveSheet.Paste
Range("G20").Select
ActiveWindow.SmallScroll Down:=-24
Windows("for copy.xlsm").Activate
End Sub

 

If anybody could help me.. I am a beginner in excel Macros and VBA.. 

 

Thanks in advance.

 

 

 

Macros and VBA

2 Replies

  • 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