Copy/Paste cells from pivot table into another workbook

Hi all,

My first post here, have had off and on Excel exposure. I am an Access and vba user/programmer. I have this Source.xlsx file that receive from another office and it loos like a pivot table with numbers and text in there, on Sheet1. I can see page breaks in there, when I go to View/Layout I see several pages. I have a second file, IMDS.xls that I need to populate with data from the first, then send out to the user.

Since I own the IMDS file I wrote all my code in it. What I would like to do is to first copy/paste special (formulas) Sheet1 of Source to Sheet2 of the Source to remove all colors, and other formatting. I can do this manually fine. Then I can just navigate on Sheet2 and select and copy/paste my cells onto the IMDS file (this part works fine using a regular .xls file to test). So I recorded a macro to copy Sheet1 to Sheet2 of Source.xlsx, and it worked. Then I added some code to find used range of Source, and introduced a public variable for that. Then I re-wrote all like below, and it worked (did copy Sheet1 onto Sheet2).

Not sure what I did next, I might have closed the files, but now when I click the button it only copies one cell and it wipes off all the field headings on the IMDS file even though it's not referenced in the code. Please see code below:

Public Sub CopyPivot()
On Error Resume Next

Dim wbk As Workbook
Set wbk = Workbooks("Source.xlsx")

wbk.Worksheets("Sheet1").Activate
ActiveSheet.ResetAllPageBreaks

Sheets("Sheet1").Select
Range(UsedRange).Select
Selection.Copy

Sheets("Sheet100").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

Public Sub FindUsedRange()
On Error Resume Next

Dim wbk As Workbook
Set wbk = Workbooks("Source.xlsx")

wbk.Worksheets("Sheet1").Activate
ActiveSheet.ResetAllPageBreaks

FirstRow = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

FirstColumn = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

UsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))

End Sub

Public Sub DoJob()
On Error Resume Next

Call FindUsedRange
Call CopyPivot

End Sub

All range variables are declared in module as public. Please help, thank you in advance.

I was able to solve this by doing several changes:

1-saved IMDS.xls file as IMDS.xlsm, to allow running code in background.
2-rather than copying a range I changed that to copy whole sheet1 to sheet2
3-I noticed that it worked if sheet1 and 2 were manually selected, so I added Cells.Select before copying.
4-probably most important, I moved all code in Module, slowly remembering how it works in Excel.

I tested this by manually selecting various ranges in Source, which before would result in coping just those cells, and it selected the whole sheet and copied over.