$$Excel-Macros$$ How can one control data entry and truly protect a verified entry
Assume one has a spreadsheet with some titles and column headings, along with a row of data already filled out by an automated copy from a previous "journal". Pardon this is long; think i have given all what i want out of this Excel "program". Assume previous journal (sheet) had a number,want this new one to be given (show) in $B$4 the next number. Want $B$3 to be current date. After that, want all cells $A$1 to $P$8 to be locked out from any attempt to change; better yet, no visiting allowed. Input cell at this time is $A$9 and input cell is advanced to $B$9; after contents are verified to be integer numeric,then that cell is locked and only then input cell is advanced for next entry and that is verified according to preset rules for that cell, which is then locked out. This continues to right up to and including $I$9; values are calculated for cells $J$9 to $P$9; and then all of those cells are locked out. At this point,row 9 is locked and this process is repeated for row 10, etc to row 33. Row 35 accumulates sums for the appropriate columns, and the user is locked out from making changes. Row 6 values and row 35 get added into row 37 and is similarly locked. Column K is calculated as a receipt number,incrementing as each row is completed by the user. Per rules above, the user is locked out and cannot change it. Rows not filled out by user remain empty. Then a new "journal"/sheet is made with old row 37 copied to new row 6, the journal number is incremented and the locking and forced data entries as mentioned continue. HOW can this be done (Excel 2003)? -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Sending Automatic Emails On Deadline : Google Doc Spreadsheet
Thanks for link, will check it.
Regards,
Hasnain
On Sat, Oct 18, 2014 at 1:34 PM, Vaibhav Joshi wrote:
> Check this will help you:
>
>
> http://www.withoutthesarcasm.com/automating-google-spreadsheets-email-reminders/
>
>
> what you can do is create a trigger which runs every hour or so which
> triggers your appointment script.
>
> In your code add condition before sending email that if current time is
> greater then event reminder time then send email, post sending email update
> in column as email sent as yes so that no new reminder send for particular
> row item..
>
>
> Thanks
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Fri, Oct 17, 2014 at 4:30 AM, Hasnain Basit
> wrote:
>
>> Hello,
>>
>> My script is in attachment. Let me explain you what it is doing.
>> I have a Google form for appointments through which user enter its data
>> and reserve two future dates for two separate appointments.
>> I need to do two tasks:
>> 1) As soon as user submits form "Confirmation Email" of form submission
>> should be send. This task is tested and completed (your previous pointer to
>> tutorial helped a lot).
>> 2) Second task is to send user "Reminder Email" One or Two day before
>> his/her reserved appointment. For this I have created two separate
>> functions 'firstAppointmentDeadLine' and 'secondAppointmentDeadLine'. These
>> functions need to be triggered one or two day before appointment. I am
>> unable to solve how to trigger these two functions.
>> Also these two functions are not tested due to trigger issue, so if you
>> find any problems please guide me as this is my first time with Google
>> forms & Java Script.
>>
>>
>> Regards,
>> Hasnain
>>
>> On Thu, Oct 16, 2014 at 6:18 PM, Vaibhav Joshi wrote:
>>
>>> Ok. Send me your whole code. Will add on time trigger.
>>> On Oct 16, 2014 2:54 PM, "Hasnain Basit" wrote:
>>>
Thanks a lot, it solved my supplementary problem but my main problem of
sending Reminder before deadline is still there. I am unable to find
solution for it.
Closest thing I found is that we can send triggers at specific time of
weekday, but since I want to send reminder near to deadline I want some
thing which can subtract deadline with current date and issue a trigger.
ScriptApp.newTrigger('myFunction')
.timeBased()
.onWeekDay(ScriptApp.WeekDay.MONDAY)
.atHour(9)
.create();
Regards,
Hasnain
On Wed, Oct 15, 2014 at 4:02 PM, Hasnain Basit
wrote:
> Hello Experts,
>
> I have a query related to Google Doc Spreadsheet.
> I have a Google Form whose data is saved in Online Google Docs
> Spreadsheet.
> Data fields saved in Google Spread sheet are (column wise) : First
> Name, Last Name, Email ID, Appointment Date.
> I want to Automatically send Reminder email to user 24 hour prior to
> their Appointment Date.
>
> I have searched few forum and found out this can be done via Excel VBA
> but could not find if this can be done via Online Google Doc, as there is
> no Developer Tab in it.
>
> Can some one guide me with better solution for this problem?
>
>
>
> Regards,
> Hasnain
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
> Advice will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners
> and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google
> Groups "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles,
Re: $$Excel-Macros$$ Pasting Data from Different Files in One Worksheet
Thanks Vaibhav Bhai. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Pasting Data from Different Files in One Worksheet
Ashish its working great, tested on 11K line items.. + *I did not do this for you. God is here working through me for you.* On Sat, Oct 18, 2014 at 7:42 PM, Ashish Kumar wrote: > HI, > > PFA.!! > > > Regards > Ashish > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Pasting Data from Different Files in One Worksheet
add this line before...
wsMain.select : wsRD.select
cheers!!
+
*I did not do this for you. God is here working through me for you.*
On Sat, Oct 18, 2014 at 7:31 PM, Hasnain Raja wrote:
> Yes exactly.
>
> On Saturday, 18 October 2014 14:29:09 UTC+1, Hasnain Raja wrote:
>>
>> Hi All,
>>
>> I am getting different files from different customers (in same format)
>> and saving it in a folder. Now to combine all the data, I am trying to
>> write a macro (vba code) which open each file in the folder copy the data
>> from first sheet and paste it in file containing the macro.
>>
>> By help form lovely people on this forum, I have come upto this code,
>> pasted underneath but I get an error at this row
>>
>> wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial
>> xlPasteValues
>>
>>
>> The code opens the files and copy the required data (which is untill
>> first blank row in column B). But It is unable to paste.
>>
>> The error I get is
>>
>> Run-time error '1004':
>>
>> Method 'Range' of object '_Worksheet' failed
>>
>> Can anyone please help me with it.
>>
>> Many Thanks :)
>>
>>
>>
>>
>> Sub CombineSheets()
>>
>>
>> Dim lstRow
>> Dim Filepath As String
>>
>>
>> Dim strFilename As String
>>
>>
>> Dim wsMain As Worksheet
>> Dim wsRD As Worksheet
>>
>>
>> Dim wbCountry As Workbook
>> Dim wsCountry As Worksheet
>>
>>
>> Set wsMain = Sheets("Main")
>> Set wsRD = Sheets("RawData")
>>
>>
>> Filepath = wsMain.Range("C5").Value
>>
>>
>> lstRow = wsRD.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
>>
>>
>> strFilename = Dir(Filepath & "\*.xlsx", vbNormal)
>>
>>
>> If Len(strFilename) = 0 Then Exit Sub
>>
>> Do Until strFilename = ""
>>
>> Set wbCountry = Workbooks.Open(Filename:=Filepath & "\" &
>> strFilename)
>> Set wsCountry = wbCountry.Worksheets(1)
>> variable = wsCountry.Cells(5, 2).End(xlDown).Row
>> wsCountry.Rows("5:" & variable).Copy
>> wbCountry.Close
>>
>>
>> lstRow = wsRD.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
>> wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial
>> xlPasteValues
>>
>>
>> strFilename = Dir()
>>
>> Loop
>>
>>
>> End Sub
>>
>> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Pasting Data from Different Files in One Worksheet
HI, PFA.!! Regards Ashish -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. For Hasnain.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Re: Pasting Data from Different Files in One Worksheet
Yes exactly.
On Saturday, 18 October 2014 14:29:09 UTC+1, Hasnain Raja wrote:
>
> Hi All,
>
> I am getting different files from different customers (in same format) and
> saving it in a folder. Now to combine all the data, I am trying to write a
> macro (vba code) which open each file in the folder copy the data from
> first sheet and paste it in file containing the macro.
>
> By help form lovely people on this forum, I have come upto this code,
> pasted underneath but I get an error at this row
>
> wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial xlPasteValues
>
>
> The code opens the files and copy the required data (which is untill first
> blank row in column B). But It is unable to paste.
>
> The error I get is
>
> Run-time error '1004':
>
> Method 'Range' of object '_Worksheet' failed
>
> Can anyone please help me with it.
>
> Many Thanks :)
>
>
>
>
> Sub CombineSheets()
>
>
> Dim lstRow
> Dim Filepath As String
>
>
> Dim strFilename As String
>
>
> Dim wsMain As Worksheet
> Dim wsRD As Worksheet
>
>
> Dim wbCountry As Workbook
> Dim wsCountry As Worksheet
>
>
> Set wsMain = Sheets("Main")
> Set wsRD = Sheets("RawData")
>
>
> Filepath = wsMain.Range("C5").Value
>
>
> lstRow = wsRD.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
>
>
> strFilename = Dir(Filepath & "\*.xlsx", vbNormal)
>
>
> If Len(strFilename) = 0 Then Exit Sub
>
> Do Until strFilename = ""
>
> Set wbCountry = Workbooks.Open(Filename:=Filepath & "\" & strFilename)
> Set wsCountry = wbCountry.Worksheets(1)
> variable = wsCountry.Cells(5, 2).End(xlDown).Row
> wsCountry.Rows("5:" & variable).Copy
> wbCountry.Close
>
>
> lstRow = wsRD.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
> wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial
> xlPasteValues
>
>
> strFilename = Dir()
>
> Loop
>
>
> End Sub
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Pasting Data from Different Files in One Worksheet
Hi,
You want to combine all files in 1 sheet.
Revert awaited.
Regards
Ashish
On 18 October 2014 18:59, Hasnain Raja wrote:
> Hi All,
>
> I am getting different files from different customers (in same format) and
> saving it in a folder. Now to combine all the data, I am trying to write a
> macro (vba code) which open each file in the folder copy the data from
> first sheet and paste it in file containing the macro.
>
> By help form lovely people on this forum, I have come upto this code,
> pasted underneath but I get an error at this row
>
> wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial xlPasteValues
>
>
> The code opens the files and copy the required data (which is untill first
> blank row in column B). But It is unable to paste.
>
> The error I get is
>
> Run-time error '1004':
>
> Method 'Range' of object '_Worksheet' failed
>
> Can anyone please help me with it.
>
> Many Thanks :)
>
>
>
>
> Sub CombineSheets()
>
>
> Dim lstRow
> Dim Filepath As String
>
>
> Dim strFilename As String
>
>
> Dim wsMain As Worksheet
> Dim wsRD As Worksheet
>
>
> Dim wbCountry As Workbook
> Dim wsCountry As Worksheet
>
>
> Set wsMain = Sheets("Main")
> Set wsRD = Sheets("RawData")
>
>
> Filepath = wsMain.Range("C5").Value
>
>
> lstRow = wsRD.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
>
>
> strFilename = Dir(Filepath & "\*.xlsx", vbNormal)
>
>
> If Len(strFilename) = 0 Then Exit Sub
>
> Do Until strFilename = ""
>
> Set wbCountry = Workbooks.Open(Filename:=Filepath & "\" & strFilename)
> Set wsCountry = wbCountry.Worksheets(1)
> variable = wsCountry.Cells(5, 2).End(xlDown).Row
> wsCountry.Rows("5:" & variable).Copy
> wbCountry.Close
>
>
> lstRow = wsRD.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
> wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial
> xlPasteValues
>
>
> strFilename = Dir()
>
> Loop
>
>
> End Sub
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Pasting Data from Different Files in One Worksheet
Hi All,
I am getting different files from different customers (in same format) and
saving it in a folder. Now to combine all the data, I am trying to write a
macro (vba code) which open each file in the folder copy the data from
first sheet and paste it in file containing the macro.
By help form lovely people on this forum, I have come upto this code,
pasted underneath but I get an error at this row
wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial xlPasteValues
The code opens the files and copy the required data (which is untill first
blank row in column B). But It is unable to paste.
The error I get is
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed
Can anyone please help me with it.
Many Thanks :)
Sub CombineSheets()
Dim lstRow
Dim Filepath As String
Dim strFilename As String
Dim wsMain As Worksheet
Dim wsRD As Worksheet
Dim wbCountry As Workbook
Dim wsCountry As Worksheet
Set wsMain = Sheets("Main")
Set wsRD = Sheets("RawData")
Filepath = wsMain.Range("C5").Value
lstRow = wsRD.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
strFilename = Dir(Filepath & "\*.xlsx", vbNormal)
If Len(strFilename) = 0 Then Exit Sub
Do Until strFilename = ""
Set wbCountry = Workbooks.Open(Filename:=Filepath & "\" & strFilename)
Set wsCountry = wbCountry.Worksheets(1)
variable = wsCountry.Cells(5, 2).End(xlDown).Row
wsCountry.Rows("5:" & variable).Copy
wbCountry.Close
lstRow = wsRD.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
wsRD.Range(Cells(lstRow, 1), Cells(lstRow, 50)).PasteSpecial
xlPasteValues
strFilename = Dir()
Loop
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Sending Automatic Emails On Deadline : Google Doc Spreadsheet
Check this will help you:
http://www.withoutthesarcasm.com/automating-google-spreadsheets-email-reminders/
what you can do is create a trigger which runs every hour or so which
triggers your appointment script.
In your code add condition before sending email that if current time is
greater then event reminder time then send email, post sending email update
in column as email sent as yes so that no new reminder send for particular
row item..
Thanks
+
*I did not do this for you. God is here working through me for you.*
On Fri, Oct 17, 2014 at 4:30 AM, Hasnain Basit
wrote:
> Hello,
>
> My script is in attachment. Let me explain you what it is doing.
> I have a Google form for appointments through which user enter its data
> and reserve two future dates for two separate appointments.
> I need to do two tasks:
> 1) As soon as user submits form "Confirmation Email" of form submission
> should be send. This task is tested and completed (your previous pointer to
> tutorial helped a lot).
> 2) Second task is to send user "Reminder Email" One or Two day before
> his/her reserved appointment. For this I have created two separate
> functions 'firstAppointmentDeadLine' and 'secondAppointmentDeadLine'. These
> functions need to be triggered one or two day before appointment. I am
> unable to solve how to trigger these two functions.
> Also these two functions are not tested due to trigger issue, so if you
> find any problems please guide me as this is my first time with Google
> forms & Java Script.
>
>
> Regards,
> Hasnain
>
> On Thu, Oct 16, 2014 at 6:18 PM, Vaibhav Joshi wrote:
>
>> Ok. Send me your whole code. Will add on time trigger.
>> On Oct 16, 2014 2:54 PM, "Hasnain Basit" wrote:
>>
>>> Thanks a lot, it solved my supplementary problem but my main problem of
>>> sending Reminder before deadline is still there. I am unable to find
>>> solution for it.
>>>
>>> Closest thing I found is that we can send triggers at specific time of
>>> weekday, but since I want to send reminder near to deadline I want some
>>> thing which can subtract deadline with current date and issue a trigger.
>>>
>>> ScriptApp.newTrigger('myFunction')
>>> .timeBased()
>>> .onWeekDay(ScriptApp.WeekDay.MONDAY)
>>> .atHour(9)
>>> .create();
>>>
>>>
>>> Regards,
>>> Hasnain
>>>
>>>
>>>
>>> On Wed, Oct 15, 2014 at 4:02 PM, Hasnain Basit
>>> wrote:
>>>
Hello Experts,
I have a query related to Google Doc Spreadsheet.
I have a Google Form whose data is saved in Online Google Docs
Spreadsheet.
Data fields saved in Google Spread sheet are (column wise) : First
Name, Last Name, Email ID, Appointment Date.
I want to Automatically send Reminder email to user 24 hour prior to
their Appointment Date.
I have searched few forum and found out this can be done via Excel VBA
but could not find if this can be done via Online Google Doc, as there is
no Developer Tab in it.
Can some one guide me with better solution for this problem?
Regards,
Hasnain
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners
and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breakin
