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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
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: 4

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

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