Databases
StackOverflow2013
Postsdbo
POMerging excel spreadsheets into one spreadsheet
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMerging excel spreadsheets into one spreadsheet
    primarykey
    data
    text
    <p>Okay, I tried to look for similar questions but I didn't understand much of what was being discussed since it's the first time I'm looking at Excel's VBA editor.</p> <p>In simple terms, I have 2 spreadsheets: "Sheet1" and "Sheet2"</p> <p>Sheet 1:</p> <pre><code> A B 1 Header1 Header2 2 Text1 Info1 3 Text2 Info2 </code></pre> <p>Sheet 2:</p> <pre><code> A B 1 Header1 Header2 2 Text3 Info3 3 Text4 Info4 </code></pre> <p>And I would like to have a macro to merge the two sheets into a new sheet (Sheet3), like this:</p> <pre><code> A B 1 Header1 Header2 2 Text1 Info1 3 Text2 Info2 4 Text3 Info3 5 Text4 Info4 </code></pre> <p>I have tried recording a macro and saving it for later use. To do this, I created a new sheet, copy/paste everything from Sheet1 to Sheet3, then copy all the information except the headings from Sheet2 to Sheet3.</p> <p>Well, the macro works for this data, but I found that the code generated by excel makes it so it selects the cell A4 (here) before pasting the data. While this works for this data, it wouldn't work if the number of records in each sheet changes now and again. Basically,</p> <p>1) I was wondering if there was a function that goes to the last relevant cell automatically before pasting the next set of data (in this example, cell A4, and if I have one more table, then cell A6).</p> <p>2) I've seen the function "ActiveCell.SpecialCells(xlLastCell).Select" (activated when I use Ctrl+End) but that carries me to the end of the sheet. I would need something similar to "Home" and "Down" arrow key after using that function for it to work best.</p> <p>Either one of those options would be good with me. ^_^</p> <p>Here's my current VBA code recorded from the Macro Recorder in excel 2010: </p> <pre><code>Sub Collate_Sheets() Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Select Sheets(Sheets.Count).Name = "Sheet3" Sheets("Sheet1").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Sheets("Sheet3").Select ActiveSheet.Paste ActiveCell.SpecialCells(xlLastCell).Select ' I need to select one cell below, and the cell in column A at this point Sheets("Sheet2").Select Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet3").Select ActiveSheet.Paste End Sub </code></pre> <p>I hope I didn't forget any useful piece of information. Let me know if I did!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJerry
    1. USJerry
    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. POMerging excel spreadsheets into one spreadsheet
      1. This table or related slice is empty.
      1. VTUpMod
    1. COOh thanks Doug! I didn't know the forum picked the adequate syntax highlighting ^^;
      singulars
      1. POMerging excel spreadsheets into one spreadsheet
      1. USJerry
    2. COTo get the row below the last truly used cell, you can write `Range("A" & Rows.Count).End(xlup).Offset(1)`. Replace `"A"` with whatever column you need.
      singulars
      1. POMerging excel spreadsheets into one spreadsheet
      1. USScott Holtzman
    3. COYou're welcome Jerry. To get the highlighting, just highlight your code and then click the code icon at the top. It looks like `{}`.
      singulars
      1. POMerging excel spreadsheets into one spreadsheet
      1. USDoug Glancy
 

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