how to copy data from one sheets to multiple sheets

dummies

New Member
Joined
Jul 10, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
i am new to vba, so it will be great help if anyone of you could help me with this.
i have to increase the the sheet based on the name in the table .
and copy data from one sheet to multiple sheets.
i have format in one sheet(template), and data in another sheet(DATA). so i have to copy only selected data from (DATA) sheet to each individual sheet .
VBA Code:
Dim ws As Worksheet, Ct As Long, c As Range

Set ws = Worksheets("原紙")
Application.ScreenUpdating = False

'//担当者シートの作成
    Dim myojiObject As Object
    Set myojiObject = CreateObject("Scripting.Dictionary")
    Set myojiObject = CreateObject("Scripting.Dictionary")
    lastRow = Sheets("DATA").Range("O" & Sheets("DATA").Rows.Count).End(xlUp).Row
    
    For i = 2 To lastRow
        If myojiObject.Exists(Sheets("DATA").Range("O" & i).Value) = False Then
            myojiObject.Add Sheets("DATA").Range("O" & i).Value, i
        End If
    Next i
    
     Dim Keys()          As Variant
   
   'crete名字シート
   Sheets.Add(After:=Sheets("人事データ")).Name = "名字"
   
    Keys = myojiObject.Keys
    With Sheets("名字")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 1 To myojiObject.Count
            .Range("A" & i + lastRow).Value = Keys(i - 1)
        Next i
    End With
    
    For Each c In Sheets("名字").Range("A2:A20")   'row to the last end
    
        If c.Value <> "" Then
    
            ws.Copy After:=Sheets(Sheets.Count)
           ActiveSheet.Name = c.Value
            Ct = Ct + 1
    
        End If
    Next c
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,815
Office Version
  1. 2010
Platform
  1. Windows
and copy data from one sheet to multiple sheets.

Do you want to get the unique values of the "O" column of the "Data" sheet?
For each unique value, copy the "Template" sheet, rename this sheet with the value?
Filter the "Data" sheet with that value, Copy the filtered data and paste into the new sheet?

If the above is correct, try the following:

VBA Code:
Sub CopyData()
  Dim shT As Worksheet, shD As Worksheet
  Dim dic As Object, a As Variant, ky As Variant
  Dim i As Long, lr As Long
  
  Set shT = Sheets("Template")
  Set shD = Sheets("Data")
  Set dic = CreateObject("Scripting.Dictionary")
  
  lr = shD.Range("O" & Rows.Count).End(3).Row
  a = shD.Range("O2:O" & lr).Value2
  
  For i = 1 To UBound(a)
    dic(a(i, 1)) = Empty
  Next
  
  For Each ky In dic.Keys
    shD.Range("A1:O" & lr).AutoFilter Columns("O").Column, ky
    shT.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = ky
    shD.AutoFilter.Range.EntireRow.Copy Range("A1")
  Next ky
  shD.ShowAllData
End Sub
 

dummies

New Member
Joined
Jul 10, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
@ Dante Amor thank you for your time.
actually i have four sheet.(1(it contains the button), 2.(sample of the table), 3.(data(have to import only selected columns according to sample in sheet 2),4.(this sheet also contains the data.))
i have to increase the sheet according to the data in sheet 4 column 2 and i am done with it.
where i am stuck is ,how to import a data from sheet 3 where i have to import only selected columns according to the table sample in sheet 2.
for example, sheet 3 contains around 20sheets but have to import only 8sheets and the data should be import/copy to multiple sheet according to the sheet name.
 

Attachments

  • コメント 2020-07-14 110942.png
    コメント 2020-07-14 110942.png
    5.1 KB · Views: 5

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,815
Office Version
  1. 2010
Platform
  1. Windows
sheet 3 contains around 20sheets but have to import only 8sheets
Sheets o columns?

i have to increase the sheet according to the data in sheet 4 column 2 and i am done with it.
I suppose you have a new code, because in the original code I don't see you using column 2.

sheet.(1(it contains the button), 2.(sample of the table), 3.(data(have to import only selected columns according to sample in sheet 2),4.(this sheet also contains the data.))
You could put a sample of your data (generic data) of what you have on each sheet.
What do you want to copy and where do you want to paste it?
Use XL2BB tool to put samples, see my signature.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,527
Messages
5,837,873
Members
430,520
Latest member
VenkateshRajaganesan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top