Report

Excel Macro's copy data from one sheet to another [Solved]

Ask a question Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Last answered on Oct 12, 2016 12:13AM
Hey Everyone

ok, so, I have a document with multiple sheets
one of the sheets is a reporting sheet
what i am trying to accomplish with not much luck is this.

on the reporting sheet, I would like to specify a date range, then pull out the rows from another sheet to the reporting sheet that are within that date range. the source data is something like

id asset serial location date

now when the rows are moved from the source sheet to the destination sheet, i only need to pull columns 2,3 and 5

The issue im having is pulling data from within a date range. All the examples I have found and managed to get working are all based on key words or values.

any help would be great. I am not a macro pro but i know my way around code.

Thanks :)
See more 
Helpful
+1
moins plus
Hello Carolusclen,

Turning ScreenUpdating Off/On should not make any difference to the code whatsoever. It basically just prevents screen flicker as Excel does its calculations.

I'm not sure what you've been up to but I do see some errors in what you've been attempting. Hence, I have altered the code a little as follows:-

Sub FindDetails()

        Dim val1 As Long: val1 = [B1]
        Dim val2 As Long: val2 = [C1]
        Dim lr As Long
        Dim ws As Worksheet
        Dim ws1 As Worksheet
Set ws = Worksheets("Report")
Set ws1 = Worksheets("Refresh Log")

Application.ScreenUpdating = False

    ws1.Range("P1", ws1.Range("P" & ws1.Rows.Count).End(xlUp)).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2
      lr = ws1.Range("P" & Rows.Count).End(xlUp).Row
         If lr > 1 Then
           Union(ws1.Range("B2:C" & lr), ws1.Range("P2:P" & lr)).Copy
             ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
               End If
                 ws1.[P1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


As you only need to reference two sheets, you'll notice that I have declared two worksheet variables and then set values to them. So, ws now wholly and solely refers to the "Report" sheet and ws1 wholly and solely refers to the "Refresh Log" sheet. In order for all to work as it should, name your sheet tabs the same. I prefer to reference sheets by their sheet codes but this appears to be causing you some confusion so I think that the above method may help you to understand more clearly the whole process.

The above code now filters Column P of the "Refresh Log" sheet (ws1) for the date range that you place in cells B1(from) and C1(to) in the "Report" sheet(ws). The relevant data is then transferred from The "Refresh Log" sheet to the "Report" sheet.
The code, as you can probably tell, excludes all other sheets and will only deal with the two sheets named in the code.

Following is the link to my updated test work book:-

https://www.dropbox.com/s/h9d9m7v9dw3y5qw/Carolusclen%28Multi%20shts%20to%20master%20sht%2C%20union%293.xlsm?dl=0

Play with the dates in the from and to (B1 & C1) cells in the "Report" sheet then click on "GO" to see it work.

Let me know which columns exactly you wish to extract the data from and I'll show you how to do it just in case your future data extraction range changes.

Test the above code in a copy of your work book first.

Place the above code in a separate module.

Cheerio,
vcoolio.
Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Oct 11, 2016 10:07PM
Hey Vcoolio

Sorry i took so long to get back to you, been unwell since Friday.

I tested it now and it works perfectly as is.

Id like to just ask a few more questions just to get a better understanding if thats ok.

are you able to break this line down and explain please

ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues


Cheers
Carolusclen
Reply
vcoolio 832Posts Thursday July 24, 2014Registration date ModeratorStatus December 8, 2016 Last seen - Oct 12, 2016 12:13AM
Hello Carolusclen,

I hope that you have fully recovered now (perhaps Excel took its toll on you!).

The line of code that you mention above is actually a part of line 17 in the last code that I supplied to you (post #11). So, these two lines:-

Union(ws1.Range("B2:C" & lr), ws1.Range("P2:P" & lr)).Copy
             ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues


are the copy and paste lines.

The line beginning with Union basically takes the data from the non-continuous cells from the source sheet in Columns B, C and P and unifies them in the destination sheet into Columns A, B & C without any gaps.

ws.Range("A" & Rows.Count).End(3)(2)


is the line of code which tells Excel where to paste in the destination sheet.
ws is the "Report" sheet as per the declared variable.

Range("A" & Rows.Count) is where the data is being pasted (Column A) counting from the very bottom row of the sheet.

.End(3)(2) tells Excel to count upwards from the very bottom row (the 3 refers to the upward direction) with the (2) being the offset of 1. So the row count is from the bottom up to the last row of data already in the destination sheet then coming back down 1 row so that the previous data is not overwritten. Hence this part of the code pastes to the next available row in the destination sheet.

.End(3)(2) is just an abbreviation for:-

.End(xlUp).Offset(1,0)
with 1 referring to the row offset and 0 the column offset (no columns to offset in this case).

The
.PasteSpecial xlPasteValues
is the method used to paste values only to the destination sheet. So, if you have formulae in the source sheet, then these formulae would be left alone and intact with only the returned values of those formulae being transferred across. If one doesn't use this piece of code and there are formulae in the source sheet, then there will be errors in the destination sheet and the formulae in the source sheet would be wiped clean.

I only placed the PasteSpecial part in the code assuming that you may have had formulae in your source sheet. If you haven't, then you won't actually need to use PasteSpecial.

I hope that this helps to enlighten you a little.

Good luck!

Cheerio,
vcoolio.
Reply
Add comment
Helpful
+0
moins plus
Hello Carolusclen,

I'm not sure if I fully follow what you would like to do however, I believe that the following code may do the task for you:-

Sub FindDetails()

        Dim val1 As Long: val1 = [B1]
        Dim val2 As Long: val2 = [C1]
        Dim lr As Long
        Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In Worksheets
If ws.Name <> "Report" Then

    ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp)).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2
      lr = ws.Range("E" & Rows.Count).End(xlUp).Row
         If lr > 1 Then
           Union(ws.Range("B2:C" & lr), ws.Range("E2:E" & lr)).Copy
             Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
               End If
ws.[E1].AutoFilter
        End If
Next ws

Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub



Basically, the code finds all dates (from all sheets) within a date range that you enter in cells B1(From) and C1(To) in the "Report" sheet and when these dates are found, the data from each relevant row (from Columns B, C & E only) are transferred to the "Report" sheet.

Following is the link to my test workbook for you to peruse:-

https://www.dropbox.com/s/dbcbx86va6uve2w/Carolusclen%28Multi%20shts%20to%20master%20sht%2C%20union%29.xlsm?dl=0

There are already two dates in cells B1 and C1 in the "Report" sheet. Click on the "GO" button to see how it works. Change the dates as you wish then click on "GO" again.

I hope that this helps.

Cheerio,
vcoolio.
Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Oct 4, 2016 01:41AM
Hey vcoolio

Thanks for this, I will give it a test later today when i have a moment to breath and let you know :). from how you explained, it sounds like its exactly what im trying to do. Ill let you know how it goes.

cheers for the reply, much appreciated

Carolusclen
Reply
Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Oct 4, 2016 11:24PM
Hey vcoolio;

Ok, so it works for a bit then it stops and deletes all the data. I think it may have something to do with all the pivot tables and other data I have in the workbook

Are you able to adjust it so that it just checks a single sheet rather than all of them :)

I tried to change the data and it keeps erroring out on me. If i use it unedited, it gives a merging error on line 16
Reply
Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Oct 5, 2016 12:35AM
Hey

Yeah, i dont believe there are any merged cells.
I would like to exclude all sheets accept the one containing the data basically. So I only want the 1 sheet called "Refresh Log" used. That one contains the data. The report sheet can still be the destination so that's fine.
Reply
Add comment
Helpful
+0
moins plus
Hello Carolusclen,

Can you advise which sheets you would like to exclude please.

Also, if there are merged cells, you will need to unmerge them as merged cells create havoc with VBA codes. You can easily reformat any sheet without merged cells.

Cheerio,
vcoolio.
Add comment
Helpful
+0
moins plus
Hello Carolusclen,

If that's the case, then, in the code, change line 11 to:-
If ws.Name = "Refresh Log" Then


I'm assuming that the data in the "Refresh Log" sheet is not in a Pivot table.

Also, change line 8 to:-

Application.ScreenUpdating=False


and change line 26 to:-

Application.ScreenUpdating=True


Test it all in a copy of your workbook first.

To prevent duplication in the "Report" sheet, do you want the "used" data in the "Refresh Log" sheet cleared once it is transferred over or do you wish to keep it all in the "Refresh Log" sheet and just refresh the "Report" sheet on each transfer?

Cheerio,
vcoolio.
Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Oct 5, 2016 09:18PM
Hey vcoolio

The data will be manually moved so that doesn't matter.

Ok, so the issue im having now is this.

I have Sheet1 with all the data
Sheet 2 has the button for the Report

What happens now is when i press the button, the data flashes and disappears from the report tab.

It seems to be placing the report data in-between all the data in Sheet1

So it looks like the code is working, just placing the results in the Report Tab and then moving it into the source tab D:

Here is what I have.

Sub FindDetails()

Dim val1 As Long: val1 = [B1]
Dim val2 As Long: val2 = [C1]
Dim lr As Long
Dim ws As Worksheet

Application.ScreenUpdating = True

For Each ws In Worksheets
If ws.Name = "Sheet1" Then

ws.Range("P2", ws.Range("P" & ws.Rows.Count).End(xlUp)).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Union(ws.Range("B2:C" & lr), ws.Range("E2:E" & lr)).Copy
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
End If
ws.[E1].AutoFilter
End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = False

End Sub
Reply
Add comment
Helpful
+0
moins plus
Hello Carolusclen,

You've made some changes that baffle me.

1) Is the column with the dates that need to be filtered Column E or Column P? Based on your opening post, you suggest that the date column is Column E. Please clarify this.

2) You have made reference to Sheet1:
If ws.Name = "Sheet1" Then


but you are transferring to Sheet1:

 Union(ws.Range("B2:C" & lr), ws.Range("E2:E" & lr)).Copy
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues


so obviously this is not going to work as you are supposedly transferring from a source sheet to a destination sheet.

Can you advise the actual names of the source and destination sheets please.

Cheerio,
vcoolio.
Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Oct 6, 2016 08:59PM
Hey vcoolio

My bad, i was messing on a clean spreadsheet with the code.
I think i am just confusing the issue. ok, so this is what i have and what i need.

I have a spreadsheet with 11 tabs. The data that needs to be checked is on a tab called "Refresh Log"

The "Refresh log" tab has a column with dates. That column is P with all the dates.

I have created a new 12th tab called Sheet1 which will be renamed later to Report

Report will have 2 cells like in your example that will contain 2 dates. The code needs to check the 2 dates, and then pull all the relevant data from "Refresh log" within that date range and put it into Sheet1.

Now with the relevant data in "Refresh log, I dont need the entire row of date. I only need (for example) Column A,B, E and J. This may change later depending on info requested.

I think I have confused myself with this code too haha. I will grab a fresh copy and see what to do again :)

Thanks for your time hey

Carolusclen
Reply
Carolusclen 8Posts Monday October 3, 2016Registration date October 11, 2016 Last seen - Oct 6, 2016 09:16PM
Sub FindDetails()

Dim val1 As Long: val1 = [B1]
Dim val2 As Long: val2 = [C1]
Dim lr As Long
Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In Worksheets
If ws.Name = "Refresh Log" Then

ws.Range("P2", ws.Range("P" & ws.Rows.Count).End(xlUp)).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2
lr = ws.Range("P" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Union(ws.Range("B2:C" & lr), ws.Range("P2:P" & lr)).Copy
Sheet12.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
End If
ws.[P1].AutoFilter
End If
Next ws

Application.CutCopyMode = False
Application.DisplayAlerts = True

End Sub



ok, i found out that
Sheet12.Range("A" & Rows.Count).
refers to the physical tab position. So now when the data pulls, it pulls from the 12th sheet, so thats ok

if i add
Application.ScreenUpdating=False
and
Application.ScreenUpdating=True
to the code, then nothing happens, if i dont add them, the data pulls from "Refresh Log" pastes into the 12th tab where the button is, then it takes it back out and pastes it into "Refresh Log" over writing column 1,2 and 3 with the filtered data hahahaha, this makes my head hurt :P
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!