Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy data of records to a templatesheet and save this template as pdffile (and then the other records one by one)

Hello, if somebody can help me out :)

I have a sheet with several records which I have to copy to a template sheet and then save/print this sheet as a pdf file on a specific place with a specific name. After the first record the next one has to follow the same rule till the last record.

Sheet1:
Column A exist "Y or N" if this record should be copied to the templatesheet for saving/print as pdf for this specific record.

If Yes in column A; (record 2 till last used);

1a) then.. copy for this specific record the data in Sheet1 ColumnD till Z to sheet2 datafield D4 with a 'transpose' action (you then get the data in Sheet2 in Column D4 till D26).

1b) Then also copy for this specific record the data in Sheet1 Column BB till BD to Sheet2 datafield D30 with a 'transpose' action (you get tje data in sheet2 in Column D30 till D33).

2a) then.. save/print the file as pdf with the filename as mentioned in Sheet1 column B of the same record (could be like 'Template-Blabla' - becomes then 'Template-Blabla.pdf').

2b) The save/print the file should be saved to the directory as mentioned in Sheet1 Column C of the same record (could be like 'C:/Apps/Templates').



After the macro is finished I'd created several pdf files as mentioned in Sheet1 where in Column A the text "Y" is registered.


Hopely explained clear. And hopely somebody to help me out.

regards, Johan







  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy data of records to a templatesheet and save this template as pdf file (and then the other records one by one)

Hi Johan,

Am Mon, 27 May 2019 08:09:44 -0700 (PDT) schrieb JS SL:

Sheet1:
Column A exist "Y or N" if this record should be copied to the templatesheet for saving/print as pdf for this specific record.

If Yes in column A; (record 2 till last used);

1a) then.. copy for this specific record the data in Sheet1 ColumnD till Z to sheet2 datafield D4 with a 'transpose' action (you then get the data in Sheet2 in Column D4 till D26).

1b) Then also copy for this specific record the data in Sheet1 Column BB till BD to Sheet2 datafield D30 with a 'transpose' action (you get tje data in sheet2 in Column D30 till D33).

2a) then.. save/print the file as pdf with the filename as mentioned in Sheet1 column B of the same record (could be like 'Template-Blabla' - becomes then 'Template-Blabla.pdf').

2b) The save/print the file should be saved to the directory as mentioned in Sheet1 Column C of the same record (could be like 'C:/Apps/Templates').


try:

Sub SaveAsPDF()
Dim varCrit As Variant, varData1 As Variant, varData2 As Variant
Dim LRow As Long, i As Long
Dim dest1 As Range, dest2 As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varCrit = .Range("A2:C" & LRow)
varData1 = .Range(.Cells(2, "D"), .Cells(LRow, "Z"))
varData2 = .Range(.Cells(2, "BB"), .Cells(LRow, "BD"))
End With
With Sheets("Sheet2")
Set dest1 = .Range("D4"): Set dest2 = .Range("D30")
For i = LBound(varCrit) To UBound(varCrit)
If varCrit(i, 1) = "Yes" Then
dest1.Resize(UBound(varData1, 2)) = _
Application.Transpose(Application.Index(varData1, i, 0))
dest2.Resize(UBound(varData2, 2)) = _
Application.Transpose(Application.Index(varData2, i, 0))
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=varCrit(i, 3) & _
varCrit(i, 2) & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy data of records to a templatesheet and save this template aspdf file (and then the other records one by one)

On Monday, May 27, 2019 at 5:09:53 PM UTC+2, JS SL wrote:
Hello, if somebody can help me out :)

I have a sheet with several records which I have to copy to a template sheet and then save/print this sheet as a pdf file on a specific place with a specific name. After the first record the next one has to follow the same rule till the last record.

Sheet1:
Column A exist "Y or N" if this record should be copied to the templatesheet for saving/print as pdf for this specific record.

If Yes in column A; (record 2 till last used);

1a) then.. copy for this specific record the data in Sheet1 ColumnD till Z to sheet2 datafield D4 with a 'transpose' action (you then get the data in Sheet2 in Column D4 till D26).

1b) Then also copy for this specific record the data in Sheet1 Column BB till BD to Sheet2 datafield D30 with a 'transpose' action (you get tje data in sheet2 in Column D30 till D33).

2a) then.. save/print the file as pdf with the filename as mentioned in Sheet1 column B of the same record (could be like 'Template-Blabla' - becomes then 'Template-Blabla.pdf').

2b) The save/print the file should be saved to the directory as mentioned in Sheet1 Column C of the same record (could be like 'C:/Apps/Templates').



After the macro is finished I'd created several pdf files as mentioned in Sheet1 where in Column A the text "Y" is registered.


Hopely explained clear. And hopely somebody to help me out.

regards, Johan


----------------------------------------------------------------

Hello Claus,
Thanks a lot. I shall implement and look if it works well and I can understand it. I allready saw also some other solutions that you answered earlier to other persons regarding 'saving as pdf'. Very nice that you help 'hobby guys' like me :) Thanks !!

In the meantime I had find another issue to solve in this same challange of data :). Perhaps you could give a solution.

In the same row as part of the loop there should follow the action;
1) Select the data in Sheet1 in column B of the active row of the loop.
For example the text is 'AAAA'.
2) Go to Sheet3 and select the data of column A/B/C where the data in column A is the same text as the selected data from Sheet1 column B.
You select then the datacolumns A/B/C where in column A the text is 'AAAA' (filtered on 'AAAA' and selection of the visible parts). Copy this data.
3) Go to Sheet2 and copy the selection to datafield C57.
This all is an action before the printout of the pdf file

regards, Johan




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy data of records to a templatesheet and save this template aspdf file (and then the other records one by one)

Hello Claus,

The part of printing pdf works realy fantastic. Thanks a lot.
The way you build it up was very clear so I was able to extend it for some other columns. THANKS A LOT.

And.... could you please have a look on the extra part that I posted earlier (thx).

regards, Johan
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy data of records to a templatesheet and save this template as pdf file (and then the other records one by one)

Hi Johan,

Am Tue, 28 May 2019 07:25:27 -0700 (PDT) schrieb JS SL:

In the same row as part of the loop there should follow the action;
1) Select the data in Sheet1 in column B of the active row of the loop.
For example the text is 'AAAA'.
2) Go to Sheet3 and select the data of column A/B/C where the data in column A is the same text as the selected data from Sheet1 column B.
You select then the datacolumns A/B/C where in column A the text is 'AAAA' (filtered on 'AAAA' and selection of the visible parts). Copy this data.
3) Go to Sheet2 and copy the selection to datafield C57.
This all is an action before the printout of the pdf file


then try:

Sub SaveAsPDF()
Dim varCrit As Variant, varData1 As Variant, varData2 As Variant
Dim LRow As Long, LRowSh3 As Long, i As Long
Dim dest1 As Range, dest2 As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varCrit = .Range("A2:C" & LRow)
varData1 = .Range(.Cells(2, "D"), .Cells(LRow, "Z"))
varData2 = .Range(.Cells(2, "BB"), .Cells(LRow, "BD"))
End With

LRowSh3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row

With Sheets("Sheet2")
Set dest1 = .Range("D4"): Set dest2 = .Range("D30")
For i = LBound(varCrit) To UBound(varCrit)
If varCrit(i, 1) = "Yes" Then
dest1.Resize(UBound(varData1, 2)) = _
Application.Transpose(Application.Index(varData1, i, 0))
dest2.Resize(UBound(varData2, 2)) = _
Application.Transpose(Application.Index(varData2, i, 0))
Sheets("Sheet3").Range("A1:C" & LRowSh3).AutoFilter field:=1, Criteria1:=varCrit(i, 2)
Sheets("Sheet3").Range("A2:C" & LRowSh3).Copy .Range("C57")
Sheets("Sheet3").AutoFilterMode = False
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=varCrit(i, 3) & _
varCrit(i, 2) & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy data of records to a templatesheet and save this template aspdf file (and then the other records one by one)

Great !! Fantastic !!
Thanks a lot :)

regards, Johan
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Copy data of records to a templatesheet and save this template as pdf file (and then the other records one by one)

Hi Johan,

Am Tue, 28 May 2019 07:25:27 -0700 (PDT) schrieb JS SL:

In the same row as part of the loop there should follow the action;
1) Select the data in Sheet1 in column B of the active row of the loop.
For example the text is 'AAAA'.
2) Go to Sheet3 and select the data of column A/B/C where the data in column
A is the same text as the selected data from Sheet1 column B. You select
then the datacolumns A/B/C where in column A the text is 'AAAA' (filtered on
'AAAA' and selection of the visible parts). Copy this data. 3) Go to Sheet2
and copy the selection to datafield C57. This all is an action before the
printout of the pdf file


then try:

Sub SaveAsPDF()
Dim varCrit As Variant, varData1 As Variant, varData2 As Variant
Dim LRow As Long, LRowSh3 As Long, i As Long
Dim dest1 As Range, dest2 As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varCrit = .Range("A2:C" & LRow)
varData1 = .Range(.Cells(2, "D"), .Cells(LRow, "Z"))
varData2 = .Range(.Cells(2, "BB"), .Cells(LRow, "BD"))
End With

LRowSh3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row

With Sheets("Sheet2")
Set dest1 = .Range("D4"): Set dest2 = .Range("D30")
For i = LBound(varCrit) To UBound(varCrit)
If varCrit(i, 1) = "Yes" Then
dest1.Resize(UBound(varData1, 2)) = _
Application.Transpose(Application.Index(varData1, i, 0))
dest2.Resize(UBound(varData2, 2)) = _
Application.Transpose(Application.Index(varData2, i, 0))
Sheets("Sheet3").Range("A1:C" & LRowSh3).AutoFilter field:=1,
Criteria1:=varCrit(i, 2) Sheets("Sheet3").Range("A2:C" &
LRowSh3).Copy .Range("C57") Sheets("Sheet3").AutoFilterMode =
False .ExportAsFixedFormat Type:=xlTypePDF, Filename:=varCrit(i,
3) & _ varCrit(i, 2) & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:=False End If
Next
End With
End Sub


Regards
Claus B.


Nice!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 49
Default Copy data of records to a templatesheet and save this template aspdf file (and then the other records one by one)

One question in the rebound :)

For the part....

Sheets("Sheet3").Range("A1:C" & LRowSh3).AutoFilter field:=1, Criteria1:=varCrit(i, 2)
Sheets("Sheet3").Range("A2:C" & LRowSh3).Copy .Range("C57")
Sheets("Sheet3").AutoFilterMode = False

What to change if the selection of varCrit(i,2) doesnt give a selection because the searched item isn't in the list. In that case theres no selection and there should be no 'copy' to do.

Something like;

If no results of selection;
Sheets("Sheet3").Range("A1:C" & LRowSh3).AutoFilter field:=1, Criteria1:=varCrit(i, 2)

Then
...
Else
Sheets("Sheet3").Range("A2:C" & LRowSh3).Copy .Range("C57")
Sheets("Sheet3").AutoFilterMode = False
End if

I'd tried something like this, but....... :(

What's the solution. Please :)

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Copy data of records to a templatesheet and save this template as pdf file (and then the other records one by one)

Hi Johan,

Am Tue, 22 Oct 2019 13:47:57 -0700 (PDT) schrieb JS SL:

For the part....

Sheets("Sheet3").Range("A1:C" & LRowSh3).AutoFilter field:=1, Criteria1:=varCrit(i, 2)
Sheets("Sheet3").Range("A2:C" & LRowSh3).Copy .Range("C57")
Sheets("Sheet3").AutoFilterMode = False

What to change if the selection of varCrit(i,2) doesnt give a selection because the searched item isn't in the list. In that case theres no selection and there should be no 'copy' to do.


try:

Sheets("Sheet3").Range("A1:C" & LRowSh3).AutoFilter field:=1, Criteria1:=varCrit(i, 2)
If Application.Subtotal(3, Sheets("Sheet3").Range("A1:A" & LRowSh3)) 1 Then
Sheets("Sheet3").Range("A2:C" & LRowSh3).Copy .Range("C57")
End If
Sheets("Sheet3").AutoFilterMode = False


Regards
Claus B.
--
Windows10
Office 2016
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete records when certain records have duplicate column data JVroom New Users to Excel 1 January 26th 09 07:23 PM
Copy Records from Access Database table(more than 5 lakh records in this table) to Excel Sheet divya Excel Programming 1 October 26th 06 12:12 PM
How do I use a template to keep records for equipment? spartanfans13 Excel Worksheet Functions 0 June 27th 06 01:56 AM
draw 999 x 8 random records from file with 8614 records news.wanadoo.nl Excel Programming 1 March 1st 06 04:04 PM
Multiple Records template madjuna Excel Discussion (Misc queries) 1 February 22nd 06 02:11 PM


All times are GMT +1. The time now is 11:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"