Databases
StackOverflow2013
Postsdbo
POExcel VBA Macro--Search For Column names and then copy into defined columns on another template worksheet in same workbook Excel 2010
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA Macro--Search For Column names and then copy into defined columns on another template worksheet in same workbook Excel 2010
    primarykey
    data
    text
    <p>I can't seem to get this to work, I don't see where there is an issue.</p> <p>It compiles fine, but it does nothing on my sheets. I am trying to write a macro that will Copy data by column header and paste into another template sheet within the same workbook with the same header. </p> <p><em>For example</em>, copy data under column "Time Started" on the import sheet, copy the new data, and paste into "Time Started" column on the Main sheet. </p> <pre><code>Sub CopyByHeader() Dim shtImport As Worksheet, shtMain As Worksheet Dim c As Range, f As Range Dim rngCopy As Range, rngCopyTo Set shtImport = ActiveSheet ' "import" - could be different workbook Set shtMain = ThisWorkbook.Sheets("Main") For Each c In Application.Intersect(shtImport.UsedRange, shtImport.Rows(1)) 'only copy if &gt;1 value in this column (ie. not just the header) If Len(c.Value) &gt; 0 And Application.CountA(c.EntireColumn) &gt; 1 Then Set f = shtMain.Rows(1).Find(what:=c.Value, LookIn:=xlValues, _ LookAt:=xlWhole) If Not f Is Nothing Then Set rngCopy = shtImport.Range(c.Offset(1, 0), _ shtImport.Cells(Rows.Count, c.Column).End(xlUp)) Set rngCopyTo = shtMain.Cells(Rows.Count, _ f.Column).End(xlUp).Offset(1, 0) 'copy values rngCopyTo.Resize(rngCopy.Rows.Count, 1).Value = rngCopy.Value End If End If Next c End Sub </code></pre> <hr> <p>I changed to this, which is super slow...any thoughts??: </p> <pre><code>Sub ImportTimeStudy() Dim myHeaders, e, x, wsImport As Worksheet, wsMain As Worksheet Dim r As Range, c As Range myHeaders = Array(Array("Time Started", "Time Started"), Array("Description of the task", "Description of the task"), Array("Level", "Level"), Array("Location", "Location"), Array("Targeted", "Targeted"), Array("System", "System"), Array("Process Code", "Process Code"), _ Array("Value Stream", "Value Stream"), Array("Subject", "Subject"), Array("BU", "BU"), Array("Task Duration", "Task Duration"), Array("Activity Code", "Activity Code")) Set wsImport = Sheets("Import") Set wsMain = Sheets("Main") For Each e In myHeaders Set r = wsImport.Cells.Find(e(0), , , xlWhole) If Not r Is Nothing Then Set c = wsMain.Cells.Find(e(1), , , xlWhole) If Not c Is Nothing Then wsImport.Range(r.Offset(1), wsImport.Cells(Rows.Count, r.Column).End(xlUp)).Copy _ wsMain.Cells(Rows.Count, c.Column).End(xlUp)(2) Else msg = msg &amp; vbLf &amp; e(1) &amp; " " &amp; wsMain.Name End If Else msg = msg &amp; vbLf &amp; e(0) &amp; " " &amp; wsImport.Name End If Next If Len(msg) Then MsgBox "Header not found" &amp; msg End If Application.ScreenUpdating = False End Sub </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRCoy1978
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel VBA Macro--Search For Column names and then copy into defined columns on another template worksheet in same workbook Excel 2010
      1. USRCoy1978
      1. VTFavorite
    1. CONote that the variable dimensions appear to be incorrect. when you type `Dim rngCopy As Range, rngCopyTo` only the first `rngCopy` is declared as a range, you are declaring the second `rngCopyTo` as a variant type variable. This doesn't directly impact your problem, but should be noted as it is a bad habbit that will cause you trouble coding down the road.
      singulars
      1. POExcel VBA Macro--Search For Column names and then copy into defined columns on another template worksheet in same workbook Excel 2010
      1. USdanielpiestrak
    2. COHave you tried stepping through the code ? If it doesn't copy any data, then some of your tests are not doing what you expect.
      singulars
      1. POExcel VBA Macro--Search For Column names and then copy into defined columns on another template worksheet in same workbook Excel 2010
      1. USTim Williams
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload