My latest project

m_in_spain

Board Regular
Joined
Sep 28, 2018
Messages
55
Hello again,
My latest project involves opening another workbook (workbook B), getting the worksheet names, copying them to a list on a sheet ("Main") in workbook A, then creating the same worksheets in workbook A. I have managed to sort this out by scratching around on the internet and putting the following together:
VBA Code:
Sub Everything()
Dim mySh As Worksheet, Ws As Worksheet
   Dim Wbk As Workbook
   Dim i As Long
   Dim x As Long
'get value from A7 to get workbook B name
   Set Wbk = Workbooks(ThisWorkbook.Sheets("Main").Range("A7").Value)
i = 4   'set at 4 so starts at 5 
   For Each Ws In Wbk.Worksheets
'get sheet names but not these specific names
   If Ws.Visible = xlSheetVisible And Ws.Name <> ("QPriceSheet") And Ws.Name <> ("TotalJobCost") And Ws.Name <> ("Break Out Prices") And Ws.Name <> ("Order Entry") Then
       i = i + 1
      ThisWorkbook.Sheets("Main").Range("C" & i).Value = Ws.Name
      Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = Ws.Name
     End If
     Next
ThisWorkbook.Worksheets("Main").Range("A:A,C:C").EntireColumn.AutoFit 'Columns A & C
End Sub

However, what i want to do is to copy data from the sheet in workbook B from cells A5 to A64 from the specific sheet, to the newly created sheet with the same name in workbook A, then move onto the next worksheet.

I have tried and tried and cannot manage to get this extra information.

As ever, any help is much appreciated.
Thanks
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,758
Hi m. You only want the range A5 to A64 from the sheets not the whole sheet? Dave
 

m_in_spain

Board Regular
Joined
Sep 28, 2018
Messages
55
Hi m. You only want the range A5 to A64 from the sheets not the whole sheet? Dave
Hi, Well i actually want to copy any data in A5 to A64 (actually A5 to last used cell before A64), i then want C5 to C64 (same, actually C5 to last used cell before C64) but i was thinking i might be able to add the second section once i understood how to do it!
Short answer to question is no, not all the sheet, just A5-A64 and C5-C64 A vould go into column A in WB A, C can go into column B in .
Thaks
M
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,758
Where goes the data in column A and B? Anything else I should know? Dave
 

m_in_spain

Board Regular
Joined
Sep 28, 2018
Messages
55

ADVERTISEMENT

Where goes the data in column A and B? Anything else I should know? Dave
Once i get the data into Workbook A, the loop through the remainder of the worksheets continues, untill all the data is copied over. The workbook A will then be saved, and used in a seperate process later.
The whole process is as follows:
From WB A, list all excel files in same folder.
Select which other file to open (WB B)
Look in WB B get list of worksheets.
Make list of sheets in WB B into WB A on Sheet ("Main")
Make same named worksheets in WB A (I got this far)
Popluate correct work sheets in WB A from data in A5-A64, & C5-C64 from WB B (Using Paste Values to get the valuse rather than any sum that may be in the cells)
I do not need any other data from WB B (at the moment!)
Save file
I have other bits of vba code that will then do other work with the data

Thanks for any help!
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,758
"From WB A, list all excel files in same folder.
Select which other file to open (WB B)"
This is quite a bit different from where U started. How do you select the file that U want to transfer data from? What happens to the old data when U transfer data from another wb to the main wb? Dave
 

m_in_spain

Board Regular
Joined
Sep 28, 2018
Messages
55

ADVERTISEMENT

"From WB A, list all excel files in same folder.
Select which other file to open (WB B)"
This is quite a bit different from where U started. How do you select the file that U want to transfer data from? What happens to the old data when U transfer data from another wb to the main wb? Dave
I have that in my first question:
the workbook to transfer data from, which i previously opened is determined from:
VBA Code:
Set Wbk = Workbooks(ThisWorkbook.Sheets("Main").Range("A7").Value)
The Old data is merely copied, rather than transferred, WB B will remain intack and be closed after i have retrieved all the into

Thanks
M
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,758
Yes I understand all that. What is the value of listing all of your files if U don't plan on accessing them? It seems like it would be better to code for the possibility that U want to gather info from those other listed wbs at some point in the future. There's not much point in starting until U know where you're going. By the way your code doesn't do anything... U need to open the other wb to get info from it. You can trial this separate sub to list your files (adjust the folderpath to suit)...
Code:
Sub LoadFileNames()
Dim LastRow As Integer, Cnt As Integer, FolderName As String
Dim FSO As Object, FLdr As Object, FileNm As Object
'*****change Foldername to your entire folder path
FolderName = ThisWorkbook.Path & "\Datafiles"

With Sheets("Main")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
If LastRow <> 1 Then
Cnt = LastRow + 1
Else
Cnt = LastRow
End If
Set FSO = CreateObject("scripting.filesystemobject")
Set FLdr = FSO.GetFolder(FolderName)
For Each FileNm In FLdr.Files
If FileNm.Name Like "*.xlsm" Then
Sheets("Main").Range("A" & Cnt) = FileNm.Path
Cnt = Cnt + 1
End If
Next FileNm

Set FLdr = Nothing
Set FSO = Nothing
End Sub
Dave
edit: Should have mentioned this list your wbs in sheet Main in "A" not sure that's what U want
 
Last edited:

m_in_spain

Board Regular
Joined
Sep 28, 2018
Messages
55
In a former part of the program, i have previously opened the file i want to access (WB B)
I am trying to make an excel file that will open another excel WB that has lots of info that i do not need to access.
I have another progran which requires the data from A5-64 and C5-64.
Eventually this WB A will be used to fill this harvested data only into yet another WB, but that is some way ahead, and i already know how to do that bit as by then the WB names, and worksheet names are known.
All the data harvested will later be condensed onto one worksheet with all the A & C data on, but i though i should do it step by step. This is just one of the steps. Harventing data from variable workbook on variable worksheets
 

Watch MrExcel Video

Forum statistics

Threads
1,127,118
Messages
5,622,831
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top