merge worksheets to a master worksheet - issue with lastrow
hello!
i am trying to merge all of my account manager's data into one master spreadsheet. i keep getting an error on the lastrow. can anyone assist with what i am doing wrong here? or information on a better way to do it?
i am a major newbie when it comes to vba, but i am trying to get this master sheet to be functional management who has very little knowledge of excel. hoping to add a button for manager to push to have updated info and this seems like the best option considering i have 7 lines of income formulas above it. 

sub mergerarsheets()
 dim sh as worksheet
 dim destsh as worksheet
 dim last as long
 dim shlast as long
 dim copyrng as range
 dim startrow as long
 dim lastrow as long

 with application
 .screenupdating = false
 .enableevents = false
 end with

 'delete the sheet "master" if it exist
 application.displayalerts = false
 on error resume next
 activeworkbook.worksheets("master").delete
 on error goto 0
 application.displayalerts = true

 'add a worksheet with the name "master"
 set destsh = activeworkbook.worksheets.add
 destsh.name = "master"

 'fill in the start row
 startrow = 9

 'loop through all worksheets and copy the data to the destsh
 for each sh in activeworkbook.sheets(array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6", "sheet7", "sheet8", "sheet9", "sheet10", "sheet11", "sheet12", "sheet13", "sheet14"))
 
 'find the last row with data on the destsh and sh
 last = lastrow(destsh)
 shlast = lastrow(sh)

 'if sh is not empty and if the last row >= startrow copy the copyrng
 if shlast > 0 and shlast >= startrow then

 'set the range that you want to copy
 set copyrng = sh.range(sh.rows(startrow), sh.rows(shlast))

 'test if there enough rows in the destsh to copy all the data
 if last + copyrng.rows.count > destsh.rows.count then
 msgbox "there are not enough rows in the destsh"
 goto exitthesub
 end if

 'this example copies values/formats, if you only want to copy the
 'values or want to copy everything look below example 1 on this page
 copyrng.copy
 with destsh.cells(last + 1, "a")
 .pastespecial xlpastevalues
 .pastespecial xlpasteformats
 application.cutcopymode = false
 end with

 end if

 end if
 next

exitthesub:

 application.goto destsh.cells(1)

 'autofit the column width in the destsh sheet
 destsh.columns.autofit

 with application
 .screenupdating = true
 .enableevents = true
 end with
end sub
ok, in that case replace these two lines
 last = lastrow(destsh)
 shlast = lastrow(sh)

with
 last = destsh.range("a" & rows.count).end(xlup).row
 shlast = sh.range("a" & rows.count).end(xlup).row
Hi Djhenry,
  

  
When you right-click a sheets tab, you can select all sheets. When you have done that, you can insert a row and enter all the information and all sheets wil contain that information.
  

  
Best regards,
  
Trowa hi, @ricky007 
create table bill\_stag
(
s\_categry varchar2(20),
s\_billnum varchar2(20)
);

insert into bill values('1111','5647859541785');
watch out! you're using two different table names there.
you don't care which m\_billnum\_seq gets assigned to which new row, do you? (you shouldn't.) in that case, you can simplify the cross join approach that cormaco suggested like this:
insert into bill\_mast (m\_bill\_seqnum, m\_billnum, channel) -- always name the columns
with  all\_channels  as
(
 select 'mob' as channel  from dual  union all
 select 'atm'      from dual
)
select  seq\_num.nextval
, s.s\_billnum
, c.channel
from     bill\_stag    s
cross join all\_channels c
;
you don't need order by, so you don't need the ord column and you don't need so an extra sub-query. i would do it like this:

https://www.advanced-excel.com/columns-to-rows.html You will probably need to write a VBA macro that does this. Formatting is not pushed down from the workbook that the user opened to the workbook that holds the source data. If I am not wrong, you are trying to read or loop through the rows from datatable. This error states that the datasource(DataTable) is not having any rows. To avoid this exception use this before you read or loop through datatable:  


```
if(dt.Rows.Count  > 0 ){
     //Do your stuff here.
}
```
  

  

  

--Amit it's not clear what it is you want as an output. if it's a row vector, there's no concatenation involved, you just simply reshape your matrix:
a = [0 1 1 1;
 1 0 1 0;
 1 0 0 1]
 v = reshape(a, 1, []) %reshape a into one row. since matlab is column major, this is done column-wise you might be able to use synchronize. if you have more than 2 tables, you will need to use it multiple times. A couple of notes to get started:



> 
> .apply(literal\_eval)
> 
> 
> 


Wouldn't this be better as `map`?



> 
> I then re-create the Dask DF:
> 
> 
> ddf = dd.from\_pandas(ddf\_out, npartitions=nCores)
> 
> 
> 


`ddf_out` was already a dask dataframe, I don't know why you had to do this.



> 
> The columns in result seem to match those in metadir.
> 
> 
> 


The value of `result.columns` is taken from the meta you provided, no computing happens until you ask for it (dask is lazy in most operations). Does the ValueError exception not provide further information?


Here is a full example



```
x = ({'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'DEBRA MEALY',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'CHAIR PERSON',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'},
 {'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'HELEN GORDON',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'VICE CHAIR',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'})
df = pd.DataFrame({'a': x})
d = dd.from_pandas(df, 1)
meta = pd.DataFrame(columns=['F9_07_PZ_COMP_DIRECT', 
       'F9_07_PZ_DIRTRSTKEY_NAME',
       'F9_07_PZ_COMP_OTHER', 'F9_07_PZ_COMP_RELATED', 'F9_07_PZ_TITLE',
       'F9_07_PZ_AVE_HOURS_WEEK', 'F9_07_PC_TRUSTEE_INDIVIDUAL'], dtype="O")
d.map_partitions(lambda df: df.a.apply(pd.Series), meta=meta).compute()

```

How did I know what `meta` to use? I applied the function to the pandas dataframe - you might use a small piece of the dataframe to do this.


Some additional notes:


* it is an anti-pattern to load the data with pandas, pass to dask workers and then gather the whole result back to a pandas (in memory) dataframe, you are very unlikely to see a speedup this way and may incur a lot of overhead. You are better off loading with something like `dd.read_csv`, and aggregating or writing with dask functions too. Only `compute()` on something that will be small or not return anything (because it involves writing the output). The official examples do not use from\_pandas.
* string and dict processing are python methods, and so hold the interpreter lock (GIL) of any python function: threads will not actually run in parallel. To get parallelism, you need to run in processes, which is most easily achieved using <https://docs.dask.org/en/latest/setup/single-distributed.html>
* the distributed scheduler also gives you access to the dashboard, which has a lot of useful information to diagnose how your system is running. You can also configure a lot about its behaviour, in case you have firewall rules you need to obey. sorry for misunderstanding the core issue with this one,@tom\_mgdi. i just tested a find row step using a simple formula (just a sum function) and the zap was able to search based on the value of the result of the formula (ie the result of the sum). this tells usthat the zap is failing specifically for query or import range formula. it’s likelynot finding the valuesbecause lookup formulas and import formulas will only show the imported value while they are being observed in google sheets, otherwisethey either appear as blank or as the raw function. if you have access to the other sheet that you are using the function to get results from, it may be worth adding an extra “lookup spreadsheet row” action to the zap, to get the information from that row in the other sheet, then combine the two rows later on. You can provide an optional parameter to the scaffolding command to update only the table you target.



```
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DirectoryNameOfYourModels -Tables employee -f

```

If you are using .net core cli then use.



```
dotnet ef dbcontext scaffold "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o DirectoryNameOfYourModels -t employee -f

```