Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 Excel VBA Copy seleted sheet Value in MasterSheet

views
     
TSKahHoong
post Jul 31 2015, 07:27 PM, updated 9y ago

Getting Started
**
Junior Member
124 posts

Joined: Sep 2011
Hi there, im a total newbie here, would like to ask a VBA question. I have lets say 7 sheets : sheet1,sheet2,sheet3,sheet4,sheet5,sheet6,Combined.
sheet1,sheet2,sheet3,sheet4 have same header category, same field, now i would like to combine them into "Combined" sheet, and only copy and paste them as Values(because those sheet all contain formula and it wasnt required). I was trying around googleing and im stuck. I have a code below, which i think i need to put PasteSpecial value somewhere. The combine sheet already have a header, it will start on A2 row, and every sheet will be pasting values on the last row of the other one. Thanks!

CODE


Option Explicit

Sub Collate_Sheets()

  Dim wks As Worksheet
  Set wks = Sheets(Sheets.Count)

   Worksheets("Combined").Activate

   With Sheets("sheet1_filter")
 
  Dim lastrow As Long

   lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
   .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)

  End With
 
   With Sheets("Sheet2_filter")

   lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
   .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)

  End With
 
   With Sheets("sheet3_filter")

   lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
   .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)

  End With
 
End Sub


heliosi
post Jul 31 2015, 10:06 PM

Getting Started
**
Junior Member
89 posts

Joined: Nov 2013
Hi,

I had a look but can't figure out how to include PasteSpecial into your one-line code.
As a quick solution you can just copy the range in Sheets("Combined") and pasted it as values.

There should be some VBA pros lurking about here that could help.

CODE


Option Explicit

' I put an ActiveX command button on the "Combined" sheet.
Private Sub CommandButton1_Click()

   Dim wks As Worksheet
   Dim lastrow As Long
   Dim i As Integer

   Set wks = Sheets(Sheets.Count)

   Worksheets("Combined").Activate

   ' loops through all sheets except last sheet (the "Combined" sheet)
   For i = 1 To (Sheets.Count - 1)
       With Sheets(i)
           lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
           .Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
       End With
   Next i
   
   ' copy the entire range of pasted values, and pastes as err..values
   With Sheets("Combined")
       lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
       .Range("A3:K" & lastrow).Copy
       .Range("A3:K" & lastrow).PasteSpecial xlPasteValues
       Application.CutCopyMode = False
   End With
   
End Sub


*

[/quote]

SincerePrayer
post Jul 31 2015, 10:48 PM

love to pray
Group Icon
Elite
1,120 posts

Joined: Jun 2005
Hi KahHoong,

Everyone was newbie once. Personally, I welcome you to the magical world of VBA. The question of you is fairly simple, if you know the strategies. smile.gif

Do you believe you can use Macro Recording to generate the code below, without even typing a single line of code? (except the comments) Feel free to check out the attached file.

I wanted to share all these strategies (and many more tricks) in Malaysia Excel Mastery gathering planned next month. This is social gathering we talk solely on using VBA and Macro to do magic in Excel. Let me know if you and your friends are interested to be part of this upcoming event. Alternatively, I could come to your company for sharing (if you have about 5 person or more). smile.gif

Again, hope this helps. biggrin.gif

» Click to show Spoiler - click again to hide... «




Attached File(s)
Attached File  LYN_Question.zip ( 17.27k ) Number of downloads: 3
TSKahHoong
post Aug 1 2015, 10:46 AM

Getting Started
**
Junior Member
124 posts

Joined: Sep 2011
QUOTE(SincerePrayer @ Jul 31 2015, 10:48 PM)
Hi KahHoong,

Everyone was newbie once. Personally, I welcome you to the magical world of VBA. The question of you is fairly simple, if you know the strategies.  smile.gif

Do you believe you can use Macro Recording to generate the code below, without even typing a single line of code? (except the comments) Feel free to check out the attached file.

I wanted to share all these strategies (and many more tricks) in Malaysia Excel Mastery gathering planned next month. This is social gathering we talk solely on using VBA and Macro to do magic in Excel. Let me know if you and your friends are interested to be part of this upcoming event. Alternatively, I could come to your company for sharing (if you have about 5 person or more). smile.gif

Again, hope this helps.  biggrin.gif

» Click to show Spoiler - click again to hide... «

*
Thanks for the help guys, but this will not do, because from my sheet1-4, they're 10000 rows, all fitted with functions. Here's the new situation, sheet1-4 is the raw data paste from various source, then sheet5 will have functions and using data from sheet 1, sheet6 will have functions and using data from sheet 2, sheet7 will have functions and using data from sheet 3,sheet8 will have functions and using data from sheet 4, (sheet 5-8, 10000 row all fitted with function, but i've use IF function (if data sheet1 is empty hence display nothing). now i have to combine sheet 5-8 to "Combined" sheet. I really need to combine selected sheets with just values. Or is there any easy way out for my condition?
SincerePrayer
post Aug 1 2015, 10:08 PM

love to pray
Group Icon
Elite
1,120 posts

Joined: Jun 2005
Hi KahHoong,

Let me rephrase you lengthy question. You want combine all sheets into one, and ignore the blanks. If this is the case, try look at my previous attached file. I use different number of rows in different sheets.

Another way, attach you working file (ZIP it) here. And, I will get the solution for you. smile.gif

Note: remove all confidential information. What you want is just solution.

Hope this helps.

QUOTE(KahHoong @ Aug 1 2015, 10:46 AM)
Thanks for the help guys, but this will not do, because from my sheet1-4, they're 10000 rows, all fitted with functions. Here's the new situation, sheet1-4 is the raw data paste from various source, then sheet5 will have functions and using data from sheet 1, sheet6 will have functions and using data from sheet 2, sheet7 will have functions and using data from sheet 3,sheet8 will have functions and using data from sheet 4, (sheet 5-8, 10000 row all fitted with function, but i've use IF function (if data sheet1 is empty hence display nothing). now i have to combine sheet 5-8 to "Combined" sheet. I really need to combine selected sheets with just values. Or is there any easy way out for my condition?
*
kent_lau7
post Aug 5 2015, 07:26 AM

Enthusiast
*****
Senior Member
767 posts

Joined: Sep 2009
From: Puchong


QUOTE(KahHoong @ Aug 1 2015, 10:46 AM)
Thanks for the help guys, but this will not do, because from my sheet1-4, they're 10000 rows, all fitted with functions. Here's the new situation, sheet1-4 is the raw data paste from various source, then sheet5 will have functions and using data from sheet 1, sheet6 will have functions and using data from sheet 2, sheet7 will have functions and using data from sheet 3,sheet8 will have functions and using data from sheet 4, (sheet 5-8, 10000 row all fitted with function, but i've use IF function (if data sheet1 is empty hence display nothing). now i have to combine sheet 5-8 to "Combined" sheet. I really need to combine selected sheets with just values. Or is there any easy way out for my condition?
*
You can watch my explanation here and download your solution here:
http://www.ugrowit.net/excel-vba-paste-all...paste-as-value/

CODE

Sub Collate_Sheets()

   ' Coded by: Kent Lau, www.ugrowit.net
   
   Dim wks As Worksheet
   Dim lastrow As Long
   
   ' Set wks = Sheets(Sheets.Count)
   Set wks = Worksheets("Combined")
   ' Worksheets("Combined").Activate
   
   lastrow = _
       Sheets("Branch01").Range("B" & Rows.Count).End(xlUp).Row
   
   ' Copy Branch01 data to Combine
   Sheets("Branch01").Range("A2:K" & lastrow).Copy
   wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1).PasteSpecial _
       Paste:=xlPasteValues 'xlPasteAll
   
   
   With Sheets("Branch02")
   
       ' Error code: lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
       lastrow = .Range("B" & Rows.Count).End(xlUp).Row
       .Range("A2:K" & lastrow).Copy
       wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1).PasteSpecial _
       Paste:=xlPasteValues
   
   End With
   
   With Sheets("Branch03")
   
       lastrow = .Range("B" & Rows.Count).End(xlUp).Row
       .Range("A2:K" & lastrow).Copy _
       wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
   
   End With
 
End Sub


 

Change to:
| Lo-Fi Version
0.0145sec    0.12    6 queries    GZIP Disabled
Time is now: 28th March 2024 - 07:15 PM